MySQL 更新连接未按预期工作
我在 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 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
SELECT
显示的数字 (1201
) 不是t1
的记录,而是来自两个表的JOIN
的行。如果两个id
不都是UNIQUE
或PRIMARY KEY
,那么这是预期的。t1
的某些行与t2
中的多行匹配。但是当UPDATE
完成后,它们只会更新一次(这是UPDATE
的MySQL“功能”或“bug”,它检查WHERE
条件在更新语句期间按顺序尝试此操作以查看应更新多少行(
t1
):The number (
1201
) that yourSELECT
shows is not records oft1
but rows from theJOIN
of two tables. If the twoid
are not bothUNIQUE
orPRIMARY KEY
s then this is expected. Some rows oft1
match multiple rows fromt2
. But when theUPDATE
is done they are only updated once (this is a MySQL "feature" or "bug" ofUPDATE
that checksWHERE
conditions sequentially during an update statement.Try this to see how many rows (of
t1
) should be updated: