CODEWITHSUNDEEP
sqlsql-server
Sam
Sam

Reputation: 61

If same value in one column in tow rows, update another column with same value from one row

Writing SQL in MS SQL server management studio.

I have this problem now, I have a table with where there are two of each row with almost identical values:

code  |   name   | location  |  group
1     |   Thing  |  1        |  1
1     |   Thing  |  2        |  NULL

I need to update the NULL GROUP to match the group that has a value, where the code is the same.

Currently in this form:

code    Locationid  ItemGroup2
100001  1           TTE
100001  2           NULL
100002  1           TTG
100002  2           NULL

I would like to update the table to match:

code    Locationid  ItemGroup2
100001  1           TTE
100001  2           TTE
100002  1           TTG
100002  2           TTG

Upvotes: 0

Views: 175

Answers (3)

mikeagg
mikeagg

Reputation: 751

This UPDATE statement should find the first non-null value of group for each code, so it should be more robust than simply assuming there will only ever be one non-null value.

UPDATE b
SET b.[group]=a.[group]
FROM MyTable b
JOIN (
    SELECT [code], [group], ROW_NUMBER() OVER(PARTITION BY [code]) as rk
    FROM MyTable WHERE [group] IS NOT NULL
) a ON a.[code]=b.[code] AND a.[rk]=1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

One method uses window functions:

with toupdate as (
      select t.*, max(grp) over (partition by code) as maxgrp
      from t
     )
update toupdate
    set grp = maxgrp
    where grp is null;

Upvotes: 1

sgeddes
sgeddes

Reputation: 62841

Here's one option joining the table to itself:

update t1
set grp = t2.grp
from yourtable t1
  join yourtable t2 on t1.code = t2.code and t2.grp is not null
where t1.grp is null

Upvotes: 1

Related Questions