Trying to insert with select from same table

OK, I can insert with a select but in doing this in the mysql query window, I am confused.

insert into mailing_lists
, email_address
, first_name
from mailing_lists
where business_id = 101

But how to I build in an ON DUPLICATE KEY UPDATE clause?


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

INSERT INTO mailing_lists (business_id, email_address, first_name) VALUES
(456, ‘’, ‘John Doe’),
(514, ‘’, ‘John Doe’),
(514, ‘’, ‘Joe Soap’),
(514, ‘’, ‘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?

, ML2.email_address
, ML2.first_name
FROM mailing_lists AS ML1
left outer
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

Do you want to have the duplicate records, or do you want to dedupe the records, or are you trying to create a unique mailing list?

You’re last query will return both records.

Is this what you are looking for?

  CASE WHEN ML.business_id = 514 THEN 456 ELSE ML.business_id END as business_id 
, ML.email_address
, ML.first_name
FROM mailing_lists AS ML
ML.business_id IN (456, 514)

If the id’s are not fixed, and you just want to keep the first id, you could do this:

  MIN(ML.business_id) as business_id 
, ML.email_address
, ML.first_name
FROM mailing_lists AS ML
, ML.first_name

thanks phobucket. I got it resolved.