IF ELSE inside of an 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)

IF ELSE inside of an UPDATE

Author

Topic

JohnJohn73
Yak Posting Veteran

57 Posts

Posted - 2007-10-16 : 17:10:45

How can I pull this one off:

UPDATE Packages
SET OperationsCompleted = OperationsCompleted+1
WHERE ID=@ParentID

I am incrementing the ops each time the package is updated through a trigger.

However, when I tried to do this:

UPDATE Packages
SET OperationsCompleted = OperationsCompleted+1
IF(OperationsCompleted=TotalOperations)
SET IsComplete=1
WHERE ID=@ParentID
I get a "Incorrect syntax near ="

Essentially I want to code this algorithm:

1. Increment the OperationsCompleted field
2. Check to see if the OperationsCompleted field matches the
3. TotalOperations field
4. If so, set the IsComplete field to 1

Not sure about nesting SETS and IFS in an update

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-16 : 17:18:02

You can't use IF inside a statement. You can use CASE though. Check BOL for details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-16 : 17:22:32

You can do it in 2 separate UPDATEs or in 1 update as follows:


UPDATE Packages
SET OperationsCompleted = OperationsCompleted+1
	,IsComplete= CASE WHEN OperationsCompleted=(TotalOperations-1) THEN 1 ELSE IsComplete END)
WHERE ID=@ParentID

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

JohnJohn73
Yak Posting Veteran

57 Posts

Posted - 2007-10-16 : 17:37:48

THanks alot for the help

At first I forgot the END, but now everything works just fine.

Boy, this website is good. People respond quickly!

Thanks again

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 01:27:01

[code]
UPDATE Packages
SET OperationsCompleted = OperationsCompleted+1
,IsComplete= CASE WHEN OperationsCompleted =(TotalOperations-1) THEN 1 ELSE IsComplete END)
WHERE ID=@ParentID
[/code]
Does that use the new, incremented value from [OperationsCompleted] or the original? I'm guessing the original ... Maybe that's what the "-1" is for but if you want the new, incremented value, you may have to do:
[code]
DECLARE @intTemp INT
UPDATE Packages
SET @intTemp = OperationsCompleted = OperationsCompleted+1
,IsComplete= CASE WHEN @intTemp =(TotalOperations-1) THEN 1 ELSE IsComplete END)
WHERE ID=@ParentID
[code]
but I ain't tested it!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources