[thelist] some SQL cause I like to share
Scott Dexter
sgd at ti3.com
Thu Jul 6 14:14:27 CDT 2000
<tip type="SQL, conditional updates">
I need to update multiple columns in one Update statement, but one of the
columns may or may not need to be updated, depending on the value it already
has. I didn't want to write two Update statements, and putting the
conditional column in the Where clause would prevent the other columns from
being updated, which is bad.
This is what I came up with, after spending some (probably too much) time in
the SQL Books Online (the MS SQL Server 7 Docs):
UPDATE tbUserPoints SET
EarnedToDate = (EarnedToDate + 100),
Balance = (Balance + 100),
AsOfDate =
(SELECT 'NewDate' =
CASE WHEN AsOfDate > '6/1/2000' THEN AsOfDate
ELSE '6/1/2000'
END)
WHERE ssn = 2
the notable part is where I'm setting the AsOfDate. Basically, if the
AsOfDate is more recent than 6/1 leave it alone, otherwise set it to 6/1. In
my code its all on one line; the line breaks above are for readability.
(if you must know, in my code, the '100', '6/1/2000', and '2' values are
variables)
</tip>
sgd
--
think safely
More information about the thelist
mailing list