On case condition update the column value

Question

Hi Expert,

Have 3 case condition and wants to update 2 field on then condition

Create table table1(col1 varchar(22),col2 varchar(22),col3 varchar(22),col4 varchar(22))

insert into table2 values ('1','2','0','0')

insert into table2 values ('3','4','0','0')

insert into table1 values ('5','4','0','0')

case when col1=1 and col2=2 then col3=X and col4= 'R1'

case when col1=3 and col2=4 then col3=Y and col4= 'R2'

case when col1=5 and col2=6 then col3=Z and col4= 'R3'

expected output

Answer 1

Try to set each column separately, e.g.

DECLARE @t TABLE (col1 varchar(22),col2 varchar(22),col3 varchar(22),col4 varchar(22))
insert into @t values ('1','2','0','0')
insert into @t values ('3','4','0','0')
insert into @t values ('5','6','0','0')
SELECT * FROM @t
UPDATE @t SET col3 = 
case when col1=1 and col2=2 then 'X' ---  and col4= 'R1'
when col1=3 and col2=4 then 'Y'-- and col4= 'R2'
when col1=5 and col2=6 then 'Z' END,
col4= case when col1=1 and col2=2 then 'R1' ---  and col4= 'R1'
when col1=3 and col2=4 then 'R2'
when col1=5 and col2=6 then 'R3' END;
SELECT * FROM @t

Answer 2

checking the solution

Answer 3

Create table table1(col1 varchar(22),col2 varchar(22),col3 varchar(22),col4 varchar(22))

insert into table1 values ('1','2','0','0')

insert into table1 values ('3','4','0','0')

insert into table1 values ('5','6','0','0')

update table1

set col3='X',col4= 'R1'

where col1=1 and col2=2 ;

update table1

set col3='Y',col4= 'R2'

where col1=3 and col2=4

update table1

set col3='Z',col4= 'R3'

where col1=5 and col2=6

select * from table1

drop table table1;

Answer 4

Hi @Shambhu Rai

Try this query, added the logic that the original value is maintained when the case condition is not met.

UPDATE #table1 
SET col3 = CASE WHEN col1=1 and col2=2 THEN 'X' 
                WHEN col1=3 and col2=4 THEN 'Y'
                WHEN col1=5 and col2=6 THEN 'Z' 
				ELSE col3 END,
    col4 = CASE WHEN col1=1 and col2=2 THEN 'R1' 
                WHEN col1=3 and col2=4 THEN 'R2'
                WHEN col1=5 and col2=6 THEN 'R3' 
				ELSE col4 END;

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.