使用sql语句将MySQL数据库内的数据从一个表传输到另一个表

Question

由于关系是一对一的，我已经整合了与其相关实体的连接表。

所以现在原来的ww_staff表直接保存了ww_contacts的细节。

我写了以下声明，基于我认为从MySQL的角度来看是合乎逻辑的
但它并不高兴。

任何人都可以看到类似的解决方案或blant海侵？

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 
); 

Answer 1

您需要执行INSERT ... SELECT ... ON DUPLICATE KEY UPDATE声明。这将插入新行并更新现有行：

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 

Answer 2

只需卸下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

因为你是从ww_contacts w JOIN ww_staff选择 - 所有记录都已经有 - 你不想重复插入，使用加入更新：

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; 

下一次请解释更多你的问题你正在尝试做什么。