I am using SQL Server in my project, and I have to update the column values of some rows based on other rows in the same table. Here is my table:
| Name | Code | Locale
--------------------
| A | ab | en
| A | cd | ar
| A | ef | ru
| B | gh | ar
I need to update Code values of the rows whose Locale is not "en" based the Code value of the row whose Locale is "en" and has the same value in Name. It is guaranteed that each row with "en" in Locale has a unique value in Name. So here is what I hope to achieve
| Name | Code | Locale
--------------------
| A | ab | en
| A | ab | ar
| A | ab | ru
| B | gh | ar
I found this thread at SO Update row with data from another row in the same table, and tried the following methods but none of them worked.
UPDATE mytable dt1, mytable dt2
SET dt1.code = dt2.code
WHERE dt1.NAME = dt2.NAME
AND dt1.code <> 'en'
AND dt2.code = 'en'
UPDATE mytable t1
INNER JOIN mytable t2 ON t1.NAME = t2.NAME and t2.code = 'en'
SET t1.code = t2.code;
WHERE t1.code <> 'en'
Update with conditionWHERE clause can be used with SQL UPDATE to add conditions while modifying records. Without using any WHERE clause, the SQL UPDATE command can change all the records for the specific columns of the table.
There are a couple of ways to do it. INSERT INTO students (id, score1, score2) VALUES (1, 5, 8), (2, 10, 8), (3, 8, 3), (4, 10, 7) ON DUPLICATE KEY UPDATE score1 = VALUES(score1), score2 = VALUES(score2);
In SQL Server, you can do this with a join
in 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;
Actually, the subquery isn't really necessary:
update t
set code = teng.code
from mytable t join
mytable teng
on teng.name = t.name and teng.locale = 'en'
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