Updating multiple rows - 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

General SQL Server Forums

New to SQL Server Programming

Updating multiple rows

Author

Topic

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-05-15 : 04:55:24

Hi

I can update single records fine. But How do I update multiple rows in a table?

Example

I have a table called Customer. In that table there are two Rows. CustomerID and CustomerIDLevel

I have a list of 300 CustomerID's that I want to replace from the current value of 0 to 3. Do they need to go in a comma delimited row? Any examples would be appreciated.

Thanks-

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2013-05-15 : 04:58:59

IS this you need?

Update table_name set current_value=3 where CustomerID in
(select CustomerID from table_name where <some condition> )

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-15 : 05:02:10

there are two approaches

1. use a condition using IN operator in WHERE and pass 300 ids in csv format

ie

WHERE CustomerID IN (ID1,ID2,...,ID300)

2. Populate a table variable with 300 IDs and do a join with it


DECLARE @CustomerIDs table
(
CustomerID int
)
INSERT @CustomerIDs
SELECT ID1 
UNION ALL
SELECT ID2
UNION ALL
SELECT ID3
...
UNION ALL
SELECT ID300


SELECT t.*
FROM YourTable t
JOIn @CustomerIDs c
ON c.CustomerID = t.CustomerID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-05-15 : 05:04:48

I think so but what I want to do is to effectively update 300 records at once. So something like this was my original thought

UPDATE Customer
SET CustomerIDLevel='3'
WHERE CustomerID='1,2,3,4,5,6,7,8,9,10';

But is it possible to have the Customer ID's in that format or do they need to be split somehow?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-15 : 05:09:42

quote:
Originally posted by deanglen

I think so but what I want to do is to effectively update 300 records at once. So something like this was my original thought

UPDATE Customer
SET CustomerIDLevel='3'
WHERE CustomerID='1,2,3,4,5,6,7,8,9,10';

But is it possible to have the Customer ID's in that format or do they need to be split somehow?

see my suggestion
you need to use IN and then pass the comma separated values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-05-15 : 05:28:45

First one worked fine. Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-15 : 05:34:16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources