使用RIGHT JOIN的MySQL UPDATE语句执行异常问题咨询

你的SQL执行失败是三层错误叠加导致的：

• 首先是操作语义完全选错：你的需求是将table_y中id未在table_x出现的行新增写入table_x，属于插入新数据的场景，而UPDATE语句只能修改表中已经存在的行，根本无法实现"新增行"的效果，从根上用错了SQL命令。
• 其次是JOIN后的筛选条件逻辑自相矛盾：你使用RIGHT JOIN关联两表时，会完整保留右表table_y的所有行，仅左表table_x的字段会在关联不上时返回NULL，你写的筛选条件WHERE y.id IS NULL永远不可能成立——右表的id字段是自身行的固有属性，不会因为左表匹配不上就变成NULL，自然查不出任何目标数据。
• 最后是赋值方向完全写反：就算筛选出了有效行，你SET子句里写的是给table_y的字段赋值为table_x的字段值，和你要往table_x写入数据的目标完全相反。

直接用INSERT ... SELECT结构即可实现需求，优先用兼容性最好的NOT EXISTS写法，支持所有主流SQL数据库：

INSERT INTO table_x (id, col1, col2, col3)
SELECT y.id, y.col1, y.col2, y.col3
FROM table_y y
WHERE NOT EXISTS (
    SELECT 1
    FROM table_x x
    WHERE x.id = y.id
);

如果是在MySQL这类支持JOIN写在SELECT子句里的数据库，也可以用LEFT JOIN写法，逻辑和上面完全一致：

INSERT INTO table_x (id, col1, col2, col3)
SELECT y.id, y.col1, y.col2, y.col3
FROM table_y y
LEFT JOIN table_x x ON y.id = x.id
WHERE x.id IS NULL;

补充：如果需要实现"id存在就更新对应字段，不存在就插入"的upsert逻辑，可以根据你用的数据库选择对应语法：MySQL用INSERT ... ON DUPLICATE KEY UPDATE，PostgreSQL用INSERT ... ON CONFLICT DO UPDATE，SQL Server/Oracle用MERGE语句即可。

内容的提问来源于stack exchange，提问作者Barnaby Cooper