how mysql update self table work

Question

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 resul…

Accepted Answer

You don't have an unique column to identifies your rows. So your`JOIN` 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

how mysql update self table work

Solution

Problem