MySQL如何更新自我表格的工作 [英] how mysql update self table work

问题描述

我在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 | 
 + ------ + ------ + 
  

我想要按列a更新列b到min（b）组。

所以SQL应该是：

  UPDATE test.temö
设置的ob =（SELECT 
 MIN（b）
 FROM test.tem I 
，其中IA = OA） 
  

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

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

  UPDATE test.tem T1 
 JOIN test.tem T2 
关于t1.a = t2.a 
设置t1.b = T2.B 
其中t1.b IS NULL 
或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; 
  

解决方案

您没有一个唯一的列，以确定您的行。因此，您的 JOIN 可能会根据您的想法更新更多的行。

---

你可能想要类似这样的东西：

  UPDATE TEM AS AS 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 TEMUES（1,2），（1,1），（1，NULL），（2,3）; 
 
 UPDATE TEM AS AS 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

I have a table in MYSQL:

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

With below data:

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

Now the data should be:

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

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

So the SQL should be:

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

But MYSQL Can't specify target table for update in FROM clause

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;

But the result is:

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

Actually the result I need is :

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

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)?

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;

解决方案

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;

See http://sqlfiddle.com/#!2/c6a04/1

---

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;

See http://sqlfiddle.com/#!2/31ffb/1

这篇关于MySQL如何更新自我表格的工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！