Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Posts
    18

    Unanswered: How to delete LEFT OUTER JOIN ... WHERE is NULL

    I have two tables that share the key mdr_report_key. I now want to delete all rows from one of the tables that don't have a common mdr_report_key in the other table.

    That is, I want to do the opposite of an INNER JOIN: rather than select the joined rows, I want to delete the rows that can't be joined. I can select the rows using this SQL

    select n.narrative_id, m.text
    from narrative_report as n
    left outer join
    medwatch_report as m
    on m.mdr_report_key=n.mdr_report_key
    where m.text is NULL
    but how do I write the DELETE from narrative_report, using the "LEFT OUTER JOIN ...where m.text is NULL" syntax? I keep getting syntax errors.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You cheat?
    Code:
    DELETE FROM narrative_report
       WHERE NOT EXISTS (SELECT 1
          FROM medwatch_report
          WHERE  medwatch_report.mdr_report_key = narrative_report.mdr_report_key)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2009
    Posts
    18
    Thanks for the reply. I hadn't thought of the correlated subquery approach.
    Last edited by kkrueck; 04-03-13 at 10:28.

Tags for this Thread

Posting Permissions

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