Trying to insert with select from same table

Databases
1

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


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

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

bazz

2

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, ‘jonny@example.com’, ‘John Doe’),
(514, ‘jonny@example.com’, ‘John Doe’),
(514, ‘joe@example.com’, ‘Joe Soap’),
(514, ‘jimmy@example.com’, ‘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?


SELECT 
  ML2.business_id
, 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
3

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 jonny@example.com records.

Is this what you are looking for?


SELECT DISTINCT
  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
WHERE
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:


SELECT 
  MIN(ML.business_id) as business_id 
, ML.email_address
, ML.first_name
FROM mailing_lists AS ML
GROUP BY 
  ML.email_address
, ML.first_name
4

thanks phobucket. I got it resolved.

bazz