title: how mysql update self table work
body: <p>I have a table in MYSQL:</p>
  
  <pre><code>CREATE TABLE test.tem(a INT,b INT);    
  </code></pre>
  
  <p>With below data:</p>
  
  <pre><code>INSERT INTO test.tem VALUES(1,2),(1,1),(1,NULL),(2,3);
  </code></pre>
  
  <p>Now the data should be:</p>
  
  <pre><code>+------+------+
  | a    | b    |
  +------+------+
  |    1 |    2 |
  |    1 |    1 |
  |    1 | NULL |
  |    2 |    3 |
  +------+------+
  </code></pre>
  
  <p>I want to update column b to the min(b) group by column a.</p>
  
  <p>So the SQL should be:</p>
  
  <pre><code>UPDATE test.tem o
  SET o.b = (SELECT
               MIN(b)
             FROM test.tem i
             WHERE i.a = o.a)
  </code></pre>
  
  <p>But MYSQL <strong>Can't specify target table for update in FROM clause</strong></p>
  
  <p>So I think below SQL can solve my question with good performance:</p>
  
  <pre><code>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 &gt; t2.b;
  </code></pre>
  
  <p>But the result is:</p>
  
  <pre><code>+------+------+
  | a    | b    |
  +------+------+
  |    1 |    1 |
  |    1 |    1 |
  |    1 |    2 |
  |    2 |    3 |
  +------+------+
  </code></pre>
  
  <p>Actually the result I need is :</p>
  
  <pre><code>+------+------+
  | a    | b    |
  +------+------+
  |    1 |    1 |
  |    1 |    1 |
  |    1 |    1 |
  |    2 |    3 |
  +------+------+
  </code></pre>
  
  <p>Question 1: Why MYSQL work out the incorrect result with the SQL? What the correct SQL with good efficient should  be?<br>
  Question 2: What the SQL should be if I only want to update b with NULL value(only update the third record)?</p>
  
  <p>About question 2, I have tried to use the incorrect SQL below:</p>
  
  <pre><code>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 &gt; t2.b;
  </code></pre>