Re: can sql CASE handle multiple column updates in a CASE statement?

On 08-May-2014 11:25 -0500, Stone, Joel wrote:

update jstone/docs00
case doctype2
when 'HEDGECONFIRMS'
set KEYWORD5 = KEYWORD1,
KEYWORD9 = KEYWORD2,
KEYWORD8 = KEYWORD3,
KEYWORD1 = ' ',
KEYWORD2 = ' ',
KEYWORD3 = ' '
when 'CONTRACTFORM'
when 'AMENDMENTFORM'

<ed:> can SQL CASE handle multiple column updates in a CASE
statement?

In the context shown, a "CASE statement" is not allowed. And a single "CASE expression" is not capable to effect what seems implied as intended, because the SET clause for an UPDATE statement can not be conditioned that way. Thus the answer to the Subject question [inserted in the <edit> just above] is No.

Or must I use many CASE statements to do this?

Multiple "CASE expressions" would be required; e.g. one per column, the _result_ of each expression assigns the value to be set for the column.

The quoted pseudo SQL [or at least non-functional SQL] from the OP is not a "CASE statement"; though, neither is that CASE a valid "CASE expression". A CASE expression can return a result, but can not be used to dynamically build a statement.

A CASE statement might be used to build a dynamic UPDATE statement that includes the appropriate column names in the SET, but the ability to react to the values of the rows [as in the above alluded desired usage] is the domain of a CASE expression. The above quoted text\SQL could be modified to concatenate the result of the above CASE expression, enclosed in apostrophes [to make a string], to the string 'update jstone/docs00', in order to define a SET clause; that dynamically built statement then could be prepared and executed.

A "CASE expression" just defines the result. Just as with any other expression, thy are allowed in an SQL statement only where calculations make sense. And an expression can not be used to modify the actual SQL statement; at least not in the manner alluded\inquired.

While Vern implied a statement might effectively be modified with a CASE expression, notably in an ORDER BY, that is a deceptive comment. Again, as just noted above, the effect for the CASE expression is just the result-value of an "expression"; i.e. a derived result that could even be nothing more than a column-name. For example [with contrived and useless static equal predicates, but the point is what the value of the "expression" is that will be collated, if a valid predicate was in effect]:

select * from qiws/qcustcdt
order by case when 1=1 then LSTNAM
end /* ELSE NULL is implied */
, case when 1=1 then UPPER(state)
end desc

Another example, but more useful and functional, and showing more conspicuously what is an "expression" for anyone that does not /see/ the prior example as such, perhaps for lack of either arithmetic or anything other than a column name and\or a scalar function:

select * from qclsrc
order by case when srcdat<400000 then 20000000+srcdat
else 19000000+srcdat /* 20th century */
end desc