CASE..THEN on UPDATE - 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

SQL Server 2005 Forums

Transact-SQL (2005)

CASE..THEN on UPDATE

Author

Topic

adjones1980
Starting Member

36 Posts

Posted - 2007-07-19 : 03:03:19

Apparently I have done something wrong in this update query near 'IS' and 'END' of the CASE statement within my update query.

I assume it has something to do with me using NULL in the wrong way but I am not sure why. Can anyone enlighten me?

UPDATE tbl_tmp_CP
		SET [tbl_tmp_CP].[Submitted] = 1, [tbl_tmp_CP].[Completed] = CASE [tbl_tmp_CP].[ValidationMsg] 
									WHEN [tbl_tmp_CP].[ValidationMsg]  IS NULL THEN 3 ELSE 2 END
		WHERE [Completed] = 1 AND
			EXISTS 
			(
				SELECT * 
				FROM #tmp
				WHERE #tmp.CP = tbl_tmp_CP.CP
			)

adjones1980
Starting Member

36 Posts

Posted - 2007-07-19 : 03:12:57

I think I got this figured out...

UPDATE tbl_tmp_CP
		SET [tbl_tmp_CP].[Submitted] = 1, [tbl_tmp_CP].[Completed] = CASE [tbl_tmp_CP].[ValidationMsg] 
									WHEN [tbl_tmp_CP].[ValidationMsg]  IS NULL THEN 3 ELSE 2 END
		WHERE [Completed] = 1 AND
			EXISTS 
			(
				SELECT * 
				FROM #tmp
				WHERE #tmp.CP = tbl_tmp_CP.CP
			)

Is this right? It is strange how some tutorials tell you to put the test column between CASE and WHEN and other don't. What is the difference?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-19 : 03:36:56

see http://msdn2.microsoft.com/en-us/library/ms181765.aspx

quote:
CASE has two formats:

* The simple CASE function compares an expression to a set of simple expressions to determine the result.
* The searched CASE function evaluates a set of Boolean expressions to determine the result.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources