MySQL - Update siblings with same key

Couldn't find a solution yet... although it is probably a newbie question, I haven't been able to overcome... hope someone can give a hand.

I have a MySQL table that has:

Blockquote

     Col1  Col2
Row1 A开发者_StackOverflow社区     null
Row2 A     A1
Row3 A     null
Row4 B     null
Row5 B     B1
etc

> Blockquote

How do I construct an SQL update to update Col2, so that the values on Col2 replace the null, ie, C2R1 and C2R3 gets A1, and C2R4 gets B1 ?

You can calculate the required values as follows:

CREATE TEMPORARY TABLE yourtemptable AS
SELECT yourtable.col1, T1.col2
FROM yourtable
JOIN
(
    SELECT col1, MAX(col2) AS col2
    FROM yourtable
    GROUP BY col1
) T1
ON yourtable.col1 = T1.col1

You can then either drop/truncate the original table and recreate it using these values, or if you can't drop the table you can instead perform a multi-table update.

Although it might not work (because the MySQL documentation states that Currently, you cannot update a table and select from the same table in a subquery.), you should try with something like:

UPDATE table1 SET col2 = (SELECT t2.col1 FROM table1 t2 WHERE t2.col1 = table1.col1 AND NOT t2.col2 IS NULL LIMIT 1) WHERE table1.col2 IS NULL