Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how mysql update self table work

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;
like image 311
bluearrow Avatar asked Aug 06 '13 12:08

bluearrow


People also ask

How does MySQL UPDATE work?

For the single-table syntax, the UPDATE statement updates columns of existing rows in the named table with new values. The SET clause indicates which columns to modify and the values they should be given. Each value can be given as an expression, or the keyword DEFAULT to set a column explicitly to its default value.


1 Answers

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

like image 53
Sylvain Leroux Avatar answered Sep 29 '22 17:09

Sylvain Leroux