CODEWITHSUNDEEP
sqlsql-server
VirVir
VirVir

Reputation: 45

To update specific column values based on other columns in mssql

I have a huge table whereby i need to update the value on one column based on the values from 2 other columns, I extract the data and put it in other new #temp table where the format of the snippet data is as follows

DOC_GUID     NAME   Value   Timestamp 
--------     ----   -----   ---------
1111         V1     AC      1134
1111         V2     AB      1134
1112         V1     N       1234
1112         V2     AB      1234
1113         V1     AC      1334
1113         V2     N       1334
1114         V1     N       1434
1114         V2     N       1434

I need to update the values to become like this

DOC_GUID     NAME   Value   Timestamp 
--------     ----   -----   ---------
1111         V1     AC      1134
1111         V2     AC      1134
1112         V1     AB      1234
1112         V2     AB      1234
1113         V1     AC      1334
1113         V2     AC      1334
1114         V1     N       1434
1114         V2     N       1434

I tried to write out the logic as follows, but it cant be executed at all

UPDATE #temp
SET Value = CASE WHEN (A.DOC_GUID = B.DOC_GUID
                      FROM #temp A inner join #temp B
                      ON A.Value= 'AC' OR 
                      B.Value = 'AC')
                 THEN 'AC'
                 WHEN (A.DOC_GUID = B.DOC_GUID
                      FROM #temp A inner join #temp B
                      ON A.Value= 'AB' OR 
                      B.Value = 'AB')
                 THEN 'AB' END

Upvotes: 0

Views: 117

Answers (3)

Michał Turczyn
Michał Turczyn

Reputation: 37367

DDL:

declare @tbl table (DOC_GUID int, NAME varchar(3), Value varchar(3),  Timestamp int );
insert into @tbl values
(1111,'V1','AC',1134),
(1111,'V2','AB',1134),
(1112,'V1','AB',1234),
(1112,'V2','N',1234),
(1113,'V1','AC',1334),
(1113,'V2','N',1334),
(1114,'V1','N',1434),
(1114,'V2','N',1434);

Update query:

update t1 set t1.Value = t2.Value
from @tbl t1 join (
    select *,
           -- here I use case statement to make AC come before AB
           row_number() over (partition by DOC_GUID order by case when [Value] = 'AC' then 'AA' else [Value] end) rn
    from @tbl
) t2 on t1.DOC_GUID = t2.DOC_GUID
where t2.rn = 1

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can do this using window function and an updatable CTE:

with toupdate as (
      select t.*,
             max(case when seqnum = 1 then value end) over (partition by doc_guid) as use_value
      from (select t.*,
                   row_number() over (partition by doc_guid order by priority) as seqnum
            from t left join
                 (values ('AC', 1),
                         ('AB', 2),
                         ('N', 3)
                 ) v(value, priority)
                 on v.value = t.value
           ) t
     )
update toupdate
    set value = use_value
    where use_value <> value;

This approach has two major advantages.

First, it is quite simple to change the priorities because these are embedded in the VALUES() clause.

Second, the outer where clause only updates the rows that need to be updated.

This should also have good performance characteristics.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

We can try using CASE along with an EXISTS clause:

UPDATE t1
SET Value = CASE WHEN EXISTS (SELECT 1 FROM #temp t2
                              WHERE t1.DOC_GUID = t2.DOC_GUID AND
                                    t2.Value = 'AC')
                 THEN 'AC'
                 WHEN EXISTS (SELECT 1 FROM #temp t2
                              WHERE t1.DOC_GUID = t2.DOC_GUID AND
                                    t2.Value = 'AB')
                 THEN 'AB'
                 ELSE 'N' END
FROM #temp t1;

Upvotes: 0

Related Questions