CODEWITHSUNDEEP
mysqldatabase
PrakashSharma
PrakashSharma

Reputation: 386

delete records from foreign-key constraint in table2 which does have primary key in table1

Suppose Table1 has

wordid col2
 1     x
 2     y
 3     x

Now table2 has

wordid col3
  1    a
  2    b
  3    c
  4    d

The "wordid" column in the "table1" table is the PRIMARY KEY in the "table1" table.

The "wordid" column in the "table2" table is a FOREIGN KEY in the "table2" table.

I want to delete all records in "table2" that are not bound to any constraint. ( here row with wordid "4" needs to be deleted)

Upvotes: 0

Views: 52

Answers (1)

Devon Bessemer
Devon Bessemer

Reputation: 35347

I'm assuming you don't have the FK set up yet or aren't enforcing integrity?

DELETE FROM table2 WHERE wordid NOT IN
( SELECT wordid FROM table1 )

Like Hamlet suggested in the comments, you should be using ON DELETE CASCADE in the FK and enforcing FK integrity.

Upvotes: 0

Related Questions