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

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 
  FR开发者_Go百科OM ww_contacts w
  JOIN ww_staff s
  ON s.staff_ID = w.contacts_ID
);

---

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.

---

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