mysql更新自我表如何工作

2022-01-17 00:00:00 sql-update sql group-by mysql

我在 MYSQL 中有一个表:

I have a table in 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 列的 min(b) 组.

I want to update column b to the min(b) group by column a.

所以 SQL 应该是:

So the SQL should be:

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

但是 MYSQL 无法在 FROM 子句中指定要更新的目标表

所以我认为下面的 SQL 可以很好地解决我的问题:

So I think below SQL can solve my question with good performance:

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应该是什么?

Question 1: Why MYSQL work out the incorrect result with the SQL? What the correct SQL with good efficient should be?
Question 2: What the SQL should be if I only want to update b with NULL value(only update the third record)?

关于问题2，我尝试使用了下面不正确的SQL:

About question 2, I have tried to use the incorrect SQL below:

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;

推荐答案

您没有唯一的列来标识您的行.所以你的JOIN 可能会像你想的那样更新更多的行.

You don't have an unique column to identifies your rows. So yourJOIN will probably update more rows as you think.

你可能想要这样的东西:

You probably want something like that instead:

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 子句的问题:

If you only want to update the rows having NULL in column b, this is only a matter of WHERE clause:

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

相关文章