Menu
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 != ''
798
First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,). Third, specify which rows you want to update in the WHERE clause.
UPDATE table SET col = ( SELECT other_col FROM other_table WHERE other_table. table_id = table.id ); Perhaps an easier way is to specify multiple tables after the UPDATE clause. Only the SET expression will perform updates but listing additional tables will allow the tables to be included.
In SQL Server, you can do this with a joinin the update. The correct syntax is: update t set code = teng.code from mytable t join (select t2.* from mytable t2 where t2.locale = 'en' ) teng on teng.name = t.name;
Updating a MySQL table with values from another table? Updating a MySQL table with values from another table? We can update another table with the help of inner join. Let us create two tables. Look at the above output, the last name is matching from the first table record. Now, I will write the query for UPDATE − Look at the sample output.
Apr 7 '11 at 0:24 @Gustav, I found a solution (see below) but yes, the intention is to have the same columns of the same NAME to have the same VALUE. ID is irrelevant – Chris B
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
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.
27
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
