slick slick - 2 months ago 8
MySQL Question

Update row with data from another row in the same table

I've got a table which looks something like this

ID | NAME | VALUE |
----------------------------
1 | Test | VALUE1 |
2 | Test2 | VALUE2 |
1 | Test2 | |
4 | Test | |
1 | Test3 | VALUE3 |


I'm looking for a way to update the values 'Test2' and 'Test' with the data from other rows in the 'VALUE' column with the same 'NAME' (The ID is not unique here, a composite key of the ID and NAME make a row unique). For example, the output I'm looking for is:

ID | NAME | VALUE |
----------------------------
1 | Test | VALUE1 |
2 | Test2 | VALUE2 |
1 | Test2 | VALUE2 |
4 | Test | VALUE1 |
1 | Test3 | VALUE3 |


If it was in another table I'd be fine, but I'm at a loss as to how I can reference a different row within the current table with the same NAME value.

Update

After modifying manji query, below is the query I used for a working solution. Thanks all!

UPDATE data_table dt1, data_table dt2
SET dt1.VALUE = dt2.VALUE
WHERE dt1.NAME = dt2.NAME AND dt1.VALUE = '' AND dt2.VALUE != ''

Answer

Try this:

UPDATE data_table t, (SELECT DISTINCT ID, NAME, VALUE
                        FROM data_table
                       WHERE VALUE IS NOT NULL AND VALUE != '') t1
   SET t.VALUE = t1.VALUE
 WHERE t.ID = t1.ID
   AND t.NAME = t1.NAME
Comments