MySQL 更新连接未按预期工作

发布于 2024-11-29 17:54:15 字数 597 浏览 4 评论 0原文

我在 ubuntu 12.10 和 MySQL Workbench 上使用 MySQL 5.1.41。

我有 2 个产品表,t1 和 t2。 t1 是实时数据,t2 是导入数据,准备更新到 t1 中以更新所有新产品价格。所以我运行:

SELECT * FROM t1
JOIN t2 ON t1.id = t2.id
WHERE t1.price != t2.price;

这将返回 1201 条记录,其中价格不同且需要更新。所以我运行:

UPDATE t1 JOIN t2 ON t1.id = t2.id
SET t1.price = t2.price
WHERE t1.price != t2.price;

这完成时没有错误并报告 1143 行受影响,匹配的行:1143 更改:1143 警告:0

所以这里已经有些东西不对了。 select 查询中有 1201 条记录不同,但使用相同的联接和条件仅更改了 1143 条记录?

运行初始选择查询,我预计会看到 58 条仍具有不同价格的记录。但是当运行它时,我得到了与最初相同的 1201。就好像更新没有被提交。

有什么想法吗?

I'm using MySQL 5.1.41 on ubuntu 12.10 and MySQL Workbench.

I have 2 product tables, t1 and t2. t1 is the live data and t2 is a imported data ready to be updated into t1 to update all the new product prices. So I run:

SELECT * FROM t1
JOIN t2 ON t1.id = t2.id
WHERE t1.price != t2.price;

This returns 1201 records where the price is different and needs to be updated. So I run:

UPDATE t1 JOIN t2 ON t1.id = t2.id
SET t1.price = t2.price
WHERE t1.price != t2.price;

This completes without error and reports 1143 row(s) affected, Rows matched: 1143 Changed: 1143 Warnings: 0

So already something here is not right. 1201 records were different in the select query, but only 1143 changed using the same join and criteria?

Running the initial select query I'd expect to see 58 records that still had different prices. But when running it I get the same 1201 as I did initially. It's as if the updates are not being committed.

Any ideas?

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

扫码二维码加入Web技术交流群

发布评论

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

评论(1

夜吻♂芭芘 2024-12-06 17:54:16

SELECT 显示的数字 (1201) 不是 t1 的记录,而是来自两个表的 JOIN 的行。如果两个 id 不都是 UNIQUEPRIMARY KEY,那么这是预期的。 t1 的某些行与 t2 中的多行匹配。但是当UPDATE完成后,它们只会更新一次(这是UPDATE的MySQL“功能”或“bug”,它检查WHERE条件在更新语句期间按顺序

尝试此操作以查看应更新多少行(t1):

SELECT * FROM t1
WHERE EXISTS
      ( SELECT *
        FROM t2
        WHERE t1.id = t2.id
          AND t1.price != t2.price
      );

The number (1201) that your SELECT shows is not records of t1 but rows from the JOIN of two tables. If the two id are not both UNIQUE or PRIMARY KEYs then this is expected. Some rows of t1 match multiple rows from t2. But when the UPDATE is done they are only updated once (this is a MySQL "feature" or "bug" of UPDATE that checks WHERE conditions sequentially during an update statement.

Try this to see how many rows (of t1) should be updated:

SELECT * FROM t1
WHERE EXISTS
      ( SELECT *
        FROM t2
        WHERE t1.id = t2.id
          AND t1.price != t2.price
      );
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文