Delete with left join

[archangel717]

Hey all,

 

I have the need to delete duplicates that are only occurring in one table. The query below is my select statement. t1 is the temp table that I want to remove duplicates. t2 is a live table that I cannot remove duplicates. My select statement pulls up both records, but I want to make sure that I am only deleting it where it occurs in t1. Please any help with this would be great!

 

select * from db1.leads as t1
left join db2.companies as t2
on t1.phone = t2.phone

 

Thanks!

[fenway]

switch "select *" to "delete t1"

[archangel717]

I'm pretty sure this deletes from the second table or both.

 

I need to only delete from the temp table and not from the live table. Obviously that would be catastrophic if I'm removing the dupes on the live table.

 

Thanks

[fenway]

I'm pretty sure this deletes from the second table or both.

Sigh.... don't believe me, believe the manual:

or the first multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted. For the second multiple-table syntax, only matching rows from the tables listed in the FROM  clause (before the USING clause) are deleted. The effect is that you can delete rows from many tables at the same time and have additional tables that are used only for searching:

 

DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;

 

Or:

 

DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;

 

These statements use all three tables when searching for rows to delete, but delete matching rows only from tables t1 and t2.

[archangel717]

Ok thanks for the code! I finally got it to work right with this final query:

 

delete t1 from leads as t1, companies as t2
where t1.phone = t2.phone;

 

The left join was not the right form of query that I needed. I was over complicating what I wanted to do.

 

Thanks again!