September 23, 2008 at 12:17 pm
I am trying to run update using CASE statement which is not working..
create table T1 (F1 int, f2 int, f3 char(2))
insert into T1 values (1, 1, 'AA')
insert into T1 values (1, 2, 'BB')
insert into T1 values (1, 3, 'CC')
insert into T1 values (1, 4, 'DD')
insert into T1 values (1, 5, 'EE')
insert into T1 values (2, 1, 'FF')
insert into T1 values (2, 2, 'GG')
insert into T1 values (2, 3, 'HH')
insert into T1 values (2, 4, 'II')
insert into T1 values (2, 5, 'JJ')
create table T2 ( F1 INT, F2 int, F21 CHAR(2), F22 CHAR(2),F23 CHAR(2),F24 CHAR(2),F25 CHAR(2))
insert into T2 values (1, 1, '','','','','')
insert into T2 values (2, 2, '','','','','')
-- Below query updates only F25 field
update
T2
SET
T2.F21 = CASE WHEN T1.f2=1 THEN T1.f3 END,
T2.F22 = CASE WHEN T1.f2=2 THEN T1.f3 END,
T2.F23 = CASE WHEN T1.f2=3 THEN T1.f3 END,
T2.F24 = CASE WHEN T1.f2=4 THEN T1.f3 END,
T2.F25 = CASE WHEN T1.f2=5 THEN T1.f3 END
FROM
T1
WHERE
T1.F1 = T2.F1
select * from T2 -- this returns only values for F25 and all others are NULL.
F1 F2 F21 F22 F23 F24 F25
----------- ----------- ---- ---- ---- ---- ----
1 1 NULL NULL NULL NULL EE
2 2 NULL NULL NULL NULL JJ
September 23, 2008 at 12:35 pm
If you don't use an ELSE in your CASE statement, whenever the expression returns false you get a NULL.
Also think about the order in which the rows might have been evaluated and then updated and you'll see why you may have received the results you have.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply