CODEWITHSUNDEEP
mysql
Matt Dykstra
Matt Dykstra

Reputation: 55

MYSQL Update from within same table

I have a table with some data that has gone wrong, that I need to fix up. Below is an example:

TABLE-A
id, type, value
1, 10, 123456
2, 10, NULL
3, 10, NULL
4, 20, 123456
5, 20, 654321
6, 20, NULL

I need a MYSQL update command.

If the "type" is the same then update the "value" so it is the same as long as the value is NULL and the "value" is unique

UPDATE table-a SET value = (...)

So in the table above only id 2 and 3 will have the value updated to 123456

id 6 will not update as the "value" is not unique for the the same "type".

Upvotes: 2

Views: 3060

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115660

UPDATE TABLE_A t
         JOIN
           ( SELECT type
                  , MIN(value) AS value
             FROM TABLE_A
             GROUP BY type
             HAVING COUNT(DISTINCT value) = 1
           ) AS tu
         ON tu.type = t.type
SET t.value = tu.value
WHERE t.value IS NULL

As Peufeu pointed, the DISTINCT is needed to catch cases like this one, where I suppose the id=3 row has to be updated, too:

TABLE-A
id | type | value
 1 |  10  | 123456
 2 |  10  | 123456
 3 |  10  | NULL
 4 |  20  | 123456
 5 |  20  | 654321
 6 |  20  | NULL

Upvotes: 4

Related Questions