4

I want to delete all rows which has no existing foreign key in another table example:

table1 +----+-------+ |id | data | +----+-------+ | 1 | hi | +----+-------+ | 2 | hi | +----+-------+ | 3 | hi | +----+-------+ | 4 | hi | +----+-------+ | 5 | hi | +----+-------+ table2 +----+-------+ |a_id| data | +----+-------+ | 1 | hi | +----+-------+ | 20 | hi | +----+-------+ | 3 | hi | +----+-------+ | 40 | hi | +----+-------+ | 5 | hi | +----+-------+

The query will delete rows with id# 20 and 40 on table2.

I need to do this so that i could establish a relationship with table1 and table2.

Improve this question
1
  • 1
    Delete From Tab2 where ID not in (Select ID From Tab1)? Commented Dec 9, 2013 at 3:02

2 Answers 2

Reset to default
12 
DELETE table2 FROM table2 LEFT JOIN table1 ON table2.a_id = table1.id WHERE table1.id IS NULL
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

4

To sum up, there are tree ways to delete multi tables

  1. NOT IN(SELECT ...) - @someone (he had deleted his answer)

    Delete From Tab2 where ID not in (Select ID From Tab1)

  2. LEFT JOIN - @eggyal

    DELETE table2 FROM table2 LEFT JOIN table1 ON table2.a_id = table1.id WHERE table1.id IS NULL

  3. NOT EXISTS

    DELETE FROM table2 WHERE NOT EXISTS ( SELECT 1 FROM table1 WHERE table1.id = table2.a_id )

According to What's the difference between NOT EXISTS vs. NOT IN vs. LEFT JOIN WHERE IS NULL?, different RDBMS perform differently.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.