CODEWITHSUNDEEP
mysql
Wildcard27
Wildcard27

Reputation: 1459

Remove rows that are already in another table

I have 2 mySQL tables. They are both lists of personal info (names, phone numbers, emails etc).

Although the field names are similar, they are not identical.

I need to remove rows from the 1st table containing phone numbers that are found in the second table.

Is this possible and can someone point me in the right direction?

Thanks in advance.

Upvotes: 0

Views: 44

Answers (3)

spencer7593
spencer7593

Reputation: 108510

First, write a query that identifies the set of rows you want to remove. For example:

SELECT o.*
  FROM table_one o
  JOIN table_two t
    ON o.phone_number = t.phone_number

Verify that this is the set of rows you want to remove. (This is also convenient if you want to store a backup copy of the rows you are going to delete. If there are multiple rows from table_two that have the same phone number, you can add a GROUP BY clause with the primary key of table_one, or a DISTINCT keyword, etc.)

Convert the SELECT statement into a DELETE statement by replacing the SELECT keyword and the select list with DELETE o.* (if it's rows from table_one you want to remove.)

Upvotes: 1

Mina Ezeet
Mina Ezeet

Reputation: 138

Try this: 

DELETE FROM `table1` WHERE `phonenumber` IN (SELECT `phonenumber` FROM `table2`)

Upvotes: 3

juergen d
juergen d

Reputation: 204924

delete t1
from first_table t1
join second_table t2 on t1.phone = t2.phone

Upvotes: 5

Related Questions