如何将一个表中的一行更新到另一个表中？

发布于 2024-12-01 02:36:57 字数 267 浏览 1 评论 0原文

我尝试将表的某些行更新到不同的表，现在我只是了解如何将行从一个表插入到另一个表，例如：

INSERT INTO dialecte_org_5.tb_data_iae (
    SELECT * FROM dialecte_org_88.tb_data_iae WHERE id_dialecte = 2413
);

除了更新之外可能有什么相同的语句？如果行存在 -> 有没有办法制作这样的东西“更新”，如果不是“插入”，

谢谢

原文

I try to update some rows of a table to a different table, for now on I just find out how to insert a row from one table to another, like:

INSERT INTO dialecte_org_5.tb_data_iae (
    SELECT * FROM dialecte_org_88.tb_data_iae WHERE id_dialecte = 2413
);

What could be the same statement but for an update?
Is there a way to make something like this, if row exists -> "update", if not "insert"

Thx

分享到QQ

分享到微博

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

发布评论

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

尹雨沫 2024-12-08 02:36:57

对于插入，您通常需要为插入语句和选择指定完整的字段列表（http://dev.mysql.com/doc/refman/5.5/en/insert.html）：

INSERT INTO dialecte_org_5.tb_data_iae (field1, field2, field3)
SELECT field1, field2, field3
FROM dialecte_org_88.tb_data_iae WHERE id_dialecte=2413;

更新语句更像是这样的（http://dev.mysql.com/doc/refman/5.0/en/update .html）：

UPDATE dialecte_org_5.tb_data_iae t1
INNER JOIN dialecte_org_88.tb_data_iae t2 ON t1.id = t2.id
SET t1.field1 = t2.field1, t1.field2 = t2.field2, t1.field3 = t2.field3
WHERE t2.id_dialecte=2413

您还可以使用 REPLACE INTO，它可以同时执行这两种操作，但它是 MySQL 特定的，其他 RDBMS 不支持（http://dev.mysql.com/doc/refman/5.0/en/replace.html）：

REPLACE INTO dialecte_org_5.tb_data_iae t1
INNER JOIN dialecte_org_88.tb_data_iae t2 ON t1.id = t2.id
SET t1.field1 = t2.field1, t1.field2 = t2.field2, t1.field3 = t2.field3
WHERE t2.id_dialecte=2413

For an insert, You usually want to specify the full field list for both the insert statement and the select (http://dev.mysql.com/doc/refman/5.5/en/insert.html):

INSERT INTO dialecte_org_5.tb_data_iae (field1, field2, field3)
SELECT field1, field2, field3
FROM dialecte_org_88.tb_data_iae WHERE id_dialecte=2413;

And an update statement is more like this (http://dev.mysql.com/doc/refman/5.0/en/update.html):

UPDATE dialecte_org_5.tb_data_iae t1
INNER JOIN dialecte_org_88.tb_data_iae t2 ON t1.id = t2.id
SET t1.field1 = t2.field1, t1.field2 = t2.field2, t1.field3 = t2.field3
WHERE t2.id_dialecte=2413

You also can use REPLACE INTO, which does both, but is MySQL specific, not supported by other RDBMS's (http://dev.mysql.com/doc/refman/5.0/en/replace.html):

REPLACE INTO dialecte_org_5.tb_data_iae t1
INNER JOIN dialecte_org_88.tb_data_iae t2 ON t1.id = t2.id
SET t1.field1 = t2.field1, t1.field2 = t2.field2, t1.field3 = t2.field3
WHERE t2.id_dialecte=2413

回复收藏 0 原文

~没有更多了~