MySQL更新使用权利加入

发布于 2025-02-11 04:40:50 字数 285 浏览 5 评论 0原文

我有一个查询，我试图从表y中添加行中的行，其中y中的ID字段不在表x中。我正在尝试使用正确的加入来执行此操作：

UPDATE table_x x

RIGHT JOIN
       table_y y
    ON y.id = x.id

SET 
y.id = x.id,
y.col1 = x.col1,
y.col2 = x.col2,
y.col3 = x.col3

WHERE y.id IS NULL

这可以作为选择语句，但由于某种原因，更新不起作用。有人知道为什么这是吗？

原文

I have a query where I am trying to add rows to table x from table y where an id field in y is not in table x. I am trying to use a RIGHT JOIN to do this:

UPDATE table_x x

RIGHT JOIN
       table_y y
    ON y.id = x.id

SET 
y.id = x.id,
y.col1 = x.col1,
y.col2 = x.col2,
y.col3 = x.col3

WHERE y.id IS NULL

This works as a SELECT statement but for some reason the UPDATE isn't working. Does anyone know why this is?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

骷髅 2025-02-18 04:40:50

如果要在表X中插入行，则可能需要插入语句而不是更新语句 -

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

If you want to Insert rows in table x you probably want an INSERT statement instead of an UPDATE statement -

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

回复收藏 0 原文

迷鸟归林 2025-02-18 04:40:50

Ankit的答案是正确的，除了WHERE子句。您想将其插入table_x时，因为table_x缺少数据，而不是Ankit的SQL中暗示的表_y。您应该在其中的x.id而不是y.id。

WHERE x.id IS NULL

因此，在table_y中有数据而不是table_x中的数据，

这将插入表_x。为了清楚起见，完整的语法将是：

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

Ankit's answer is correct except for the WHERE clause. You want to insert into table_x as table_x is missing data, not table_y as is implied in Ankit's SQL. Hence, you should have x.id instead of y.id in the WHERE:

WHERE x.id IS NULL

This will insert into table_x wherever there is data in table_y and not in table_x.

For clarity, the full syntax would therefore be:

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

回复收藏 0 原文

~没有更多了~