|
View previous topic :: View next topic
|
| Author |
Message |
Karthikeyan Venkatadri
New User
Joined: 29 Jun 2010
Posts: 6
Location: Chennai
|
|
|
|
Hi,
I have the following query :
UPDATE TableA
SET C1 =
CASE
WHEN VAR ='Y' THEN 10
END,
C2 =
CASE
WHEN VAR ='Y' THEN 20
END
WHERE C4 = 2
If my VAR <> 'Y', then i need to skip my update for the columns C1 and C2. My columns are Not Nullable. My update throws a -407 due to this. Can this be achieved in any other manner? |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
seems a little ass-backwards to involve the CASE.
why not
| Code: |
UPDATE TableA
SET C1 = 10
,C2 = 20
WHERE C4 = 2
AND VAR = 'Y'
|
and learn to use BBCode |
|
| Back to top |
|
|
Karthikeyan Venkatadri
New User
Joined: 29 Jun 2010
Posts: 6
Location: Chennai
|
|
|
|
Hi Dick,
Sorry for the confusion. VAR is not a column. Its a working storage variable. Depending on the value of the variable i need to update my columns. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello and welcome to the forum,
Then move the VAR test outside the sql. . .
| Code: |
IF VAR = Y then
UPDATE. . . . ? |
as DBZ suggested? |
|
| Back to top |
|
|
Karthikeyan Venkatadri
New User
Joined: 29 Jun 2010
Posts: 6
Location: Chennai
|
|
|
|
Sorry again. I think am messing things a bit. These are 2 different variables.
UPDATE TableA
SET C1 =
CASE
WHEN VAR-1 ='Y' THEN 10
END,
C2 =
CASE
WHEN VAR-2 ='Y' THEN 20
END
WHERE C4 = 2
My exact scenario : I have 7 different variables like Var-1 and var-2. Depending on the value of each variable i need to update 7 coumns. To avoid multiple queries for each combination of the 7 variables, i was trying to club everything into one query. Is there a way i can do it at all?? or do i need to write separate update queries for each combination? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
if VAR is always the same VAR then dbz solution still stands , or even solve it in your program :
| Code: |
if var = Y then
exec sql update..
end-exec
end-if |
if you somehow have these VARs per column you'll need to do something like this
| Code: |
...
C1 = CASE WHEN :VAR-c1 ='Y' THEN 10 else C1 END
,C2 = CASE WHEN :VAR-c2 ='Y' THEN 20 else C2 END
... |
|
|
| Back to top |
|
|
Karthikeyan Venkatadri
New User
Joined: 29 Jun 2010
Posts: 6
Location: Chennai
|
|
|
|
Hi GuyC,
Thank a lot. My variables are per column. The second solution worked. |
|
| Back to top |
|
|
|
|
