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 != ''
This question is related to
mysql
If you just need to insert a new row with a data from another row,
insert into ORDER_ITEM select * from ORDER_ITEM where ITEM_NUMBER =123;
Update MyTable
Set Value = (
Select Min( T2.Value )
From MyTable As T2
Where T2.Id <> MyTable.Id
And T2.Name = MyTable.Name
)
Where ( Value Is Null Or Value = '' )
And Exists (
Select 1
From MyTable As T3
Where T3.Id <> MyTable.Id
And T3.Name = MyTable.Name
)
UPDATE financialyear
SET firstsemfrom = dt2.firstsemfrom,
firstsemto = dt2.firstsemto,
secondsemfrom = dt2.secondsemfrom,
secondsemto = dt2.secondsemto
from financialyear dt2
WHERE financialyear.financialyearkey = 141
AND dt2.financialyearkey = 140
Here's my go:
UPDATE test as t1
INNER JOIN test as t2 ON
t1.NAME = t2.NAME AND
t2.value IS NOT NULL
SET t1.VALUE = t2.VALUE;
EDIT: Removed superfluous t1.id != t2.id
condition.
Source: Stackoverflow.com