MYSQL Update from within same table

Question

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".

what should be value of "id"6 in above example as it can be updated to either 123456 or 654321? — WordsWorth
– WordsWorth, Commented Jul 2, 2011 at 10:28
It should not be updated, I will have to manually update these. — Matt Dykstra
– Matt Dykstra, Commented Jul 2, 2011 at 10:30
I would not suggest you do it in a single query.A single query can be complex.Use PHP or some other language,you are working with. — WordsWorth
– WordsWorth, Commented Jul 2, 2011 at 10:36

ypercubeᵀᴹ · Accepted Answer · 2011-07-02 10:57:32Z

4

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

edited Jul 2, 2011 at 10:57

answered Jul 2, 2011 at 10:50

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

bobflux Over a year ago

count(value) is wrong here : you meant count(DISTINCT value) I guess.

ypercubeᵀᴹ Over a year ago

@peufeu: Yeah, I guess COUNT(DISTINCT value) is more correct. It will catch cases where for a type there are two or more rows with exact same value and all other rows are NULL. Thnx.

Matt Dykstra Over a year ago

works well for my example, thanks. however if the table has 7,30,123456 8,30,123456 9,30,NULL then ID 9 does not update

Collectives™ on Stack Overflow

MYSQL Update from within same table

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related