我在MysqL中有一个表：
CREATE TABLE test.tem(a INT,b INT);
以下数据：
INSERT INTO test.tem VALUES(1,2),(1,1),NULL),(2,3);
现在数据应该是：
+------+------+
| a | b |
+------+------+
| 1 | 2 |
| 1 | 1 |
| 1 | NULL |
| 2 | 3 |
+------+------+
我想按列a将列b更新到min(b)组.
所以sql应该是：
UPDATE test.tem o
SET o.b = (SELECT
MIN(b)
FROM test.tem i
WHERE i.a = o.a)
但是MysqL无法在FROM子句中指定更新目标表
UPDATE test.tem t1
JOIN test.tem t2
ON t1.a = t2.a
SET t1.b = t2.b
WHERE t1.b IS NULL
OR t1.b > t2.b;
但结果是：
+------+------+
| a | b |
+------+------+
| 1 | 1 |
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
+------+------+
其实我需要的结果是：
+------+------+
| a | b |
+------+------+
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 2 | 3 |
+------+------+
问题1：为什么MysqL使用sql计算出错误的结果？具有高效率的正确sql应该是什么？
问题2：如果我只想用NULL值更新b(仅更新第三条记录),sql应该是什么？
关于问题2,我试图使用下面不正确的sql：
UPDATE test.tem t1
JOIN test.tem t2
ON t1.a = t2.a
AND t1.b IS NULL
SET t1.b = t2.b
WHERE t1.b IS NULL
OR t1.b > t2.b;
最佳答案
您没有唯一列来标识行.因此,yourJOIN可能会按您的意愿更新更多行.
你可能想要这样的东西：
UPDATE tem AS t1 JOIN (SELECT a,MIN(b) AS m FROM tem GROUP BY a) AS t2
USING (a)
SET t1.b = t2.m;
见http://sqlfiddle.com/#!2/c6a04/1
如果您只想更新列b中具有NULL的行,这只是WHERE子句的问题：
CREATE TABLE tem(a INT,b INT);
INSERT INTO tem VALUES(1,3);
UPDATE tem AS t1 JOIN (SELECT a,MIN(b) AS m FROM tem GROUP BY a) AS t2
USING (a)
SET t1.b = t2.m
WHERE t1.b IS NULL;