How to dedupe between two tables

[Zych]

OK, I have been going a little nuts in trying to find info on deduping. I am somewhat new to SQL and generaly use it on a single table. This is what I have:

Table 1 -> first name (FName), last name (LName), street (Street), city (City), state (State), zip (Zip), and phone number (Phone)

Table 2 -> Phone numbers (Phone)

This is what I want to do:

I would like to delete all records in table one in which the telephone number has a match in table two. When done no records in table one should have a match in table two. Another option would be to have a field added to table one in which can be used as a mark for deletion.

Any help would be appreciated.

Thanks,

Zych

 

[swampBoogie]

delete from table1 where phone in
(select phone from table2)

or the update variant

update table1 set deleted = true -- or 'Yes'/1 or ...
where phone in
(select phone from table2)

 

[carp]

DELETE FROM table2
WHERE phone IN (SELECT phone FROM table1);

or

DELETE FROM table2 t2
WHERE EXISTS (SELECT 'x'
FROM table1
WHERE phone = t2.phone);

 

[Zych]

Thanks for the quick reply. I will try that. I did not even no you could nest a sql select inside an IN statement like that.

Thanks Again,

Zych

 

[carp]

Well, whether you can or not will depend on your RDBMS. Good luck.