I don't think I need the on duplicate key update now.
what I am actually trying to do is merge the records from two identical tables, ensuring there are no dupes. The tables are identical but the records aren't completely.
here's my tables structures
create table mailing_lists
( id int not null
, email_address varchar(99)
, first_name varchar(99)
So far, I have put all the records into the one table, where the difference between old and new, is the ID
(456, 'email@example.com', 'John Doe'),
(514, 'firstname.lastname@example.org', 'John Doe'),
(514, 'email@example.com', 'Joe Soap'),
(514, 'firstname.lastname@example.org', 'Jimmy Dee'),
So, my query needs to look in the table for records with one of two ids, 456 OR 514 and convert 514 to 456, where it will not cause a duplicate or error because it has created one.
So, for the data above, all ids should become 456 but the second record should be overlooked. I can then remove all those with the id of 514 afterwards.
here is a part query. Am I on the right lines?
FROM mailing_lists AS ML1
join mailing_lists AS ML2
on ML1.email_address = ML2.email_address
where ML1.business_id != ML2.business_id
and ML1.business_id = 456
and ML2.business_id = 514