Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011

    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.

  2. #2
    Join Date
    Jul 2011
    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 
      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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts