On 08-May-2014 11:25 -0500, Stone, Joel wrote:
set KEYWORD5 = KEYWORD1,
KEYWORD9 = KEYWORD2,
KEYWORD8 = KEYWORD3,
KEYWORD1 = ' ',
KEYWORD2 = ' ',
KEYWORD3 = ' '
<ed:> can SQL CASE handle multiple column updates in a CASE
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
select * from qiws/qcustcdt
order by case when 1=1 then LSTNAM
end /* ELSE NULL is implied */
, case when 1=1 then UPPER(state)
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 */