CODEWITHSUNDEEP
phpmysqldatabase
Tirenty2
Tirenty2

Reputation: 59

Compare to tables in a mysql-database

I've got two tables in a mysql-database.

table "names":

ID | name
100 | smith
136 | king
224 | brown
485 | miller

and the second table "adresses":

name_ID | adress  
100 | mainstreet 11
101 | hiddleburger street 33
102 | great avenue 1022
103 | panorama avenue 687
... | ...

In table "names" I've got 4 entries (ID 100, 136, 224, 485). In table "adresses" I've got a few hundret entries.

Now I want to delete all the entries from adresses that have no matching names in table "names". So I want to delete all adress-entries, that are not name_ID 100,136,224,485.

How can I do that? How can I compare those to tables?

Thank you!

Upvotes: 2

Views: 47

Answers (1)

David Jenkins
David Jenkins

Reputation: 471

NOT IN and a subquery. Something like this perhaps:

delete from addresses where name_ID NOT IN (select ID from names);

performance may not be so good, but it will work.

Upvotes: 1

Related Questions