MySQL如何更新自身表格

Question

我在MYSQL中有一张表：

CREATE TABLE test.tem(a INT,b INT);    

以下是数据:

INSERT INTO test.tem VALUES(1,2),(1,1),(1,NULL),(2,3);

现在数据应该是这样的：

+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    1 |    1 |
|    1 | NULL |
|    2 |    3 |
+------+------+

我希望更新B列的数据为A列分组后B列的最小值。

所以SQL语句应该是：

UPDATE test.tem o
SET o.b = (SELECT
             MIN(b)
           FROM test.tem i
           WHERE i.a = o.a)

但是MYSQL不支持在FROM子句中指定目标表进行更新操作。

因此，我认为以下SQL可以高效地解决我的问题：

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：如果我只想将b更新为NULL值（仅更新第三条记录），应该使用什么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;

- bluearrow

Answer 1

你没有一个唯一的列来标识你的行。因此，你的JOIN可能会更新更多的行，超出你的预期。

---

你可能想要像这样的东西：

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,2),(1,1),(1,NULL),(2,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;

请查看http://sqlfiddle.com/#!2/31ffb/1。

- Sylvain Leroux

---

使用你的SQL语句更新一个有450万条记录的表需要大约5分钟时间，能否帮我找出SQL语句的错误？ - bluearrow

@bluearrow 由于这是一个不同的主题，您应该提出另一个问题，提供您的表结构（包括索引），请求及其查询计划。 - Sylvain Leroux

期待您的回答：http://stackoverflow.com/questions/18117717/mysql-join-update-internal-steps - bluearrow

Answer 2

将其写成 JOIN 形式

：

UPDATE tem
JOIN ( SELECT a, MIN(b) AS min_b FROM tem GROUP BY a ) AS mins USING (a)
SET tem.b = mins.min_b ;

- RandomSeed

---

这是一个不错的方法。但我认为mins表没有索引。你能帮我找出我的SQL错误吗？ - bluearrow

Answer 3

您可以使用临时表来完成这个操作：

create temporary table tem2 (a INT, b INT);

insert into tem2 
  select a, min(b) from tem group by a;

update tem
  inner join tem2 on tem.a = tem2.a
  set tem.b = tem2.b;

drop table tem2;

我认为应该可以。'drop table'并不是必须的，因为当连接关闭时，tem2将被自动删除，尽管这样做是好习惯。

- james-geldart