Can CASE Be Used to Do This? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Can CASE Be Used to Do This?

Author

Topic

BobRoberts
Posting Yak Master

109 Posts

Posted - 2011-10-29 : 16:34:41

What I'm trying to do:

For any row that matches @ID_KEY, I want to set [QTY_ORD] equal to @QTY_ORD and [PURCHASE] equal to @PURCHASE. "Purchase" is a bit column. However, and this is what I'm trying to use the CASE for, if the @PURCHASE is "False", then I want to set the [QTY_ORD] to zero. Can this be done in one UPDATE statement?

I've made an attempt at doing it below, I'm getting an "incorrect syntax near '='" when this gets run:

UPDATE [QPURCH] SET [QTY_ORD] = CASE @PURCHASE WHEN @PURCHASE = 0 THEN 0 ELSE @QTY_ORD END, [PURCHASE] = @PURCHASE WHERE [ID_KEY] = @ID_KEY

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-10-29 : 21:29:53

There are 2 forms of CASE, you have tried to use both in one statement which is not allowed. You either have the simple case expression, which is:

CASE {expression}
WHEN {value} THEN {value}
ELSE {value}
END

Or, you have this version:

CASE WHEN {expression} THEN {value}
WHEN {expression} THEN {value}
ELSE {value}
END

Your statement probably should be:


UPDATE qpurch
   SET qty_ord = CASE WHEN @purchase = 0
                      THEN 0
                      ELSE @qty_ord
                  END
       ,purchase = @purchase
 WHERE id_key = @id_key;

Jeff

BobRoberts
Posting Yak Master

109 Posts

Posted - 2011-10-30 : 12:10:04

Yes, I see - I don't need the "@PURCHASE" after CASE. Thanks a lot - I'll give this a try.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources