Unanswered: Delete from multiple tables in one query.
I have two tables having foreign key relation.
Hoe should I delete from the two tables.
I tried with -
DELETE FROM schemaname.tablele1 AS TableRef1,
schemaname.tablele2 AS TableRef2 WHERE
TableRef1.DEP_DATE = TableRef2.DEP_DATE
AND (TableRef1.DEP_DATE >= DATE('04-07-2011')
AND (TableRef1.DEP_DATE <= DATE('20-07-2011'))
AND TableRef1.LAST_MSG_TYPE = 'ASD';
I am getting the error as ',' canmot be there.
Please suggest a Query to perform the above.
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:
delete from schemaname.tablele2
where DEP_DATE in
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.