update all based on parameter - 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)

update all based on parameter

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-12-09 : 09:26:25

I have a update sp with some parameters, like:
@ItemId int,
@ROTM bit

When @ROTM is true, I want the whole column be set to 'false' first, and then only the row for ItemID be set to true.

When @ROTM is false, then it does not matter.

How can I do this.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-09 : 09:36:22

update tbl
set ROTM = case when ItemId = @ItemId then 1 else 0 end
where @ROTM = 1

or
if @ROTM = 1
update tbl
set ROTM = case when ItemId = @ItemId then 1 else 0 end

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2010-12-09 : 09:38:19

Something like:


UPDATE U
SET MyColumn = CASE WHEN MyItemID = @ItemId THEN True ELSE False END
FROM MyTable AS U
WHERE (MyItemID = @ItemId OR @ROTM = 1)
      AND (MyColumn IS NULL OR MyColumn <> CASE WHEN MyItemID = @ItemId THEN True ELSE False END) -- Optimisation

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-12-09 : 09:43:41

I'm now trying two updates, so:

if @ROTM = 1
update
[Table]
set
[ROTM] = 0
end

UPDATE
[Table]
SET
[ROTM] = @ROTM,
...etc.
Where
ItemId = @ItemID

However, it says: Incorrect syntax near the keyword 'UPDATE' for the second update

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-09 : 10:05:05

it's because of the end before it.
You know that the if only acts for the following statement? You need a begin/end for multiple statements.
Did you read my previous post or Kristen's?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-12-09 : 10:18:17

Yes, I left out the 'end' so now it works...
Thanks for your help...

The secret to creativity is knowing how to hide your sources. (Einstein)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-09 : 10:36:30

If you are doing that you might want to include a transaction and error handling.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2010-12-09 : 10:47:43

Note also that "your way" will be updating rows that haven't changed which will bloat your logs and slow the system down.

"you might want to include a transaction"

I would say "definitely" needs to do that as otherwise the record will be in inconsistent state after the first update, but before the second (which is why you & I suggested a single ATOMic statement

)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources