Normally you'd do this through two queries, though you could send them in the same batch to get the same effect. I don't know of any SQL dialects that allow joins in a delete command.
I'm not sure which of your tables depends on which, but you'd delete in reverse order. If tablele2 has a FK linking to tablele1, you'd delete rows from tablele2 first.
This is all based on inference of your table structure from your query, so it may not be exactly what you want, but something like:
Code:
delete from schemaname.tablele2
where DEP_DATE in
(
select t1.DEP_DATE
from schemaname.tablele1 t1
where t1.DEP_DATE >= DATE('04-07-2011')
and t1.DEP_DATE <= DATE('20-07-2011')
and t1.LAST_MSG_TYPE = 'ASD'
);
delete from schemaname.tablele1
where DEP_DATE >= DATE('04-07-2011')
and DEP_DATE <= DATE('20-07-2011')
and LAST_MSG_TYPE = 'ASD'
;
In the first query you get a list from tablele1 based on your deletion criteria, and delete all records in tablele2 with DEP_DATE values matching those from the tablele1 subquery.
In the second query you delete the tablele1 records.
dBforums
>
Delete from multiple tables in one query.
07-19-11, 03:22
Linear Mode
