CODEWITHSUNDEEP
sqlsql-server
curious1
curious1

Reputation: 14717

SQL Server : update rows based other rows in the same table

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'

Upvotes: 7

Views: 7482

Answers (2)

ughai
ughai

Reputation: 9880

Another way using sub-query

UPDATE LocaleTable
   SET Code = ISNULL((SELECT TOP 1 Code FROM LocaleTable t WHERE t.Name = LocaleTable.Name and t.Locale = 'en' ORDER BY Code),Code)
WHERE Locale <> 'en'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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'

Upvotes: 14

Related Questions