CODEWITHSUNDEEP
sqlsql-server
Anton V Kovalchuk
Anton V Kovalchuk

Reputation: 35

SQL Server update multiple record from list of records

I'm trying find way to update data from some list of records.

I have to update format for ~1000 serial numbers, the list contains old and new values for SN. It looks like: 

A012-234 =SSM10145-01
...
A986-477 = SSM15845-52

I do it one by one.

DECLARE @PrevID char(25);
DECLARE @NewID char(25); 

SET @PrevID = 'A012-234';
SET @NewID = 'SSM10145-01'; 

UPDATE [dbo].[Device]
SET 
      ,[DeviceTypeID] = 1
      ,[Description] = @NewID
      ,[DeviceSN] = @NewID
      ,[LogLevel] = 0
WHERE DeviceSN = @PrevID
GO

How update all records in one request?

Thank you.

Upvotes: 2

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can use join in update. So:

with vals as (
      select v.*
      from (values ('A012-234', 'SSM10145-01'),
                   ('A986-477', 'SSM15845-52')
           ) v(previd, newid)
     )
update d
   set DeviceTypeID = 1,
       Description = v.newid,
       DeviceSN = v.newid
       LogLevel = 0
from device d join
     vals v
     on d.DeviceSN = v.previd;

If the old and new values are coming from a table, you can just plug the table directly into the query.

Upvotes: 2

Related Questions