CODEWITHSUNDEEP
mysql
MRA
MRA

Reputation: 277

How do I delete values in MySQL according to primary key on another table

Example my db structure olddb & newdb

newdb

ID email           Name
-----------------------
1  [email protected]  John
2  [email protected] Peter

olddb

ID email           
-------------------
1  [email protected]  
2  [email protected] 
3  [email protected]
4  [email protected]

Now I want compare olddb with newdb and delete from olddb which email doesn't have email in newdb

Let me know 

DELETE FROM olddb, newdb
USING olddb
    INNER JOIN newdb USING(email) 
WHERE olddb.email <> newdb.email

Upvotes: 0

Views: 41

Answers (2)

bfavaretto
bfavaretto

Reputation: 71939

This will give you a list of all emails from oldtbl which are not on newtbl:

SELECT * 
FROM oldtbl
WHERE id NOT IN (
   SELECT id FROM newtbl
)

Note: oldtbl and newtbl because I found your naming scheme confusing. We are talkind about tables, not databases.

To delete those rows on oldtbl, just change SELECT * with DELETE.

Upvotes: 0

Ignacio
Ignacio

Reputation: 5730

DELETE FROM oldb WHERE olddb.email NOT IN (SELECT email FROM newdb);

There are probably other ways to do it, but that will work.

Upvotes: 1

Related Questions