[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