Transfering data within a MySQL db from one table to another with sql statement

Question

I have consolidated a joined table with it's related entity as the relationship was one-to-one.

So now the original ww_staff table holds the ww_contacts details directly.

I wrote the following statement based on what I think is logical from MySQL's perspective
but - its not happy.

Can anyone see a similar solution or a blatent transgression?

INSERT INTO 
  ww_staff s 
  (phone, mobile, email, skype) 
VALUES 
(
  SELECT w.phone, w.mobile, w.email, w.skype 
  FROM ww_contacts w
  JOIN ww_staff s
  ON s.staff_ID = w.contacts_ID
);

"Not happy" is not a valid MySQL error message. In this case it's pretty obvious (see Scrum Meister's answer), but please remember that for future posts — , Dec 04 '10 at 09:44

The Scrum Meister · Answer 1 · 2010-12-04T20:32:15.837

2

Just remove the VALUES()

INSERT INTO ww_staff s (phone, mobile, email, skype) 
SELECT w.phone, w.mobile, w.email, w.skype FROM ww_contacts w
JOIN ww_staff s
ON s.staff_ID = w.contacts_ID;

--UPDATE

Since you are selecting from ww_contacts w JOIN ww_staff - all the records are there already - and you do not want to insert duplicates, use a update with a join:

UPDATE ww_staff s JOIN ww_contacts w ON s.staff_ID = w.contacts_ID
SET s.phone = w.phone, s.mobile = w.mobile, s.email = w.email, s.skype = w.skype;

Next time please explain more in your question what you are trying to do.

edited Dec 04 '10 at 20:32

answered Dec 04 '10 at 08:20

The Scrum Meister

29,681
8
66
64

Unexpected results! - It seems that I cannot add these fields (phone, mobile, email, skype) to the existing records (staff_ID) - the records are duplicated each time with a new staff_ID. Anyone know why? – SaminOz Dec 04 '10 at 10:00
each time you run the query you are duplicating the table. what *are* you trying to do??? – The Scrum Meister Dec 04 '10 at 20:00
@Jonathan Amend - I can see where I needed to explain my requirements more clearly. Apologies, I won't make the same mistake twice. Both solutions work fine. Thanks. – SaminOz Dec 06 '10 at 08:41

Jonathan Amend · Accepted Answer · 2010-12-04T20:24:08.823

You need to do an INSERT ... SELECT ... ON DUPLICATE KEY UPDATE statement. This will insert new rows and update existing ones:

INSERT INTO 
  ww_staff
  (staff_ID, phone, mobile, email, skype) 
SELECT w.contacts_ID, w.phone, w.mobile, w.email, w.skype 
  FROM ww_contacts w
  JOIN ww_staff s
  ON s.staff_ID = w.contacts_ID
ON DUPLICATE KEY UPDATE
 ww_staff.phone = w.phone, ww_staff.mobile = w.mobile, ww_staff.email = w.email, ww_staff.skype = w.skype

Transfering data within a MySQL db from one table to another with sql statement

2 Answers2