View previous topic :: View next topic
|
Author |
Message |
Jeya Raj
New User
Joined: 14 Jan 2007 Posts: 33 Location: USA
|
|
|
|
Hello,
Could you please let me know is it posible to do a paritial column update when using UPDATE with CASE statement like below?
UPDATE A SET C1(1:2) =
CASE
WHEN C2 = 'CSE' AND C1(1:1) = '1' THEN '01'
WHEN C2 = 'CSE' AND C1(1:1) = '2' THEN '02'
END ;
Thank you. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
you can, but your syntax is wrong:
UPDATE A SET C1 =
WHEN C2 = 'CSE' AND C1(1:1) = '1' THEN '01' || RIGHT(C1, LENGTH(C1) - 2,OCTETS)
WHEN C2 = 'CSE' AND C1(1:1) = '2' THEN '02' || RIGHT(C1, LENGTH(C1) - 2,OCTETS)
ELSE C1
END
When using CASE expressions, besure to have an ELSE for those rows that are not true for any of the WHENs.
you should add:
ELSE C1 |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
the above only works if the length of column C1 is > 2.
hopefully it is some variation of CHAR.
will not work for a numeric or date/time column. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
C1(1:1) ? this is SQL, not cobol
try
Code: |
case when substr(C1,1,1) in ('1','2') then '0' || substr(name,1,1) || substr(name,3) else C1 end |
|
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
ah well my syntax was not much better. |
|
Back to top |
|
|
Jeya Raj
New User
Joined: 14 Jan 2007 Posts: 33 Location: USA
|
|
|
|
Thank you gentlemen. It worked very well. I really appriciate your help!!! |
|
Back to top |
|
|
|