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 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;

Solution 1

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

Solution 2

Write it as a JOIN instead:

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 ;