UPDATE QUERY - 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 QUERY

Author

Topic

SimeF
Starting Member

2 Posts

Posted - 2010-01-27 : 14:10:43

Hi,

I would like to update multiple rows with different values in on go using a combined query.
Would someone please confirm that my approach and syntax is correct.

Greatly appreciated.

USE DB1
GO

UPDATE Table1 SET ValPct = '18.7450' FROM Table1 WHERE Table1.Code = 'SID051' AND Emp = '004448'
UPDATE Table1 SET ValPct = '13.3890' FROM Table1 WHERE Table1.Code = 'SID051' AND Emp = '005089'
UPDATE Table1 SET ValPct = '10.7120' FROM Table1 WHERE Table1.Code = 'SID051' AND Emp = '0U1239'
UPDATE Table1 SET ValPct = '133.8940' FROM Table1 WHERE Table1.Code = 'SID051' AND Emp = '0U1396'
UPDATE Table1 SET ValPct = '13.3900' FROM Table1 WHERE Table1.Code = 'SID053' AND Emp = '004428'
UPDATE Table1 SET ValPct = '26.7790' FROM Table1 WHERE Table1.Code = 'SID053' AND Emp = '005087'
UPDATE Table1 SET ValPct = '27.4850' FROM Table1 WHERE Table1.Code = 'SID028' AND Emp = '004472'
UPDATE Table1 SET ValPct = '29.4490' FROM Table1 WHERE Table1.Code = 'SID002' AND Emp = '005072'
UPDATE Table1 SET ValPct = '35.3380' FROM Table1 WHERE Table1.Code = 'SID004' AND Emp = '005073'
UPDATE Table1 SET ValPct = '23.5590' FROM Table1 WHERE Table1.Code = 'SID002' AND Emp = '005092'

Am i required to place a semi colon or "GO" after each row?

Thanks in advance.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-27 : 14:28:58

not required.

should also remove the FROM clause

UPDATE Table1 SET ValPct = '18.7450' WHERE Code = 'SID051' AND Emp = '004448'

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 14:31:01

Looks OK

"Am i required to place a semi colon or "GO" after each row?"

No

If not then they will all be treated as a single batch. If one fails it will abort the whole batch and all will be rolled back. If you put GO after each one any that fail will only fail that one Update - all the other updates will have worked (generally this is a bad thing because you then have to work out what worked, and what did not work, before trying it again.

SimeF
Starting Member

2 Posts

Posted - 2010-01-27 : 14:32:48

Thank you both very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources