CODEWITHSUNDEEP
sqlsql-server
KyloRen
KyloRen

Reputation: 2741

Update multiple records with differing where and set values?

I am trying to write a statement that will update multiple records into a table in a single SQL statement. I know there is multiple SO threads and other web sites addressing this batch Insert statements, but I can't find one that I can understand enough for me to manipulate the statement to work for me.

The single Update statement is simple enough

UPDATE [dbo].[ShiftTimes]
SET SubSection = 'One'
WHERE ID = '9E3C491A-3FE1-47F1-B701-000009C08CD0'

And to illustrate what I am trying to do (With incorrect syntax)

UPDATE [dbo].[ShiftTimes]
SET SubSection = 'One', 'Two', 'Three'
WHERE ID = '9E3C491A-3FE1-47F1-B701-000009C08CD0', '10F9F862-4723-4105-A8E9-000017097EBE', '25810E61-8E86-432B-9387-00003048E74D'

So basically I am trying to do this, in one statement

Where ID = '9E3C491A-3FE1-47F1-B701-000009C08CD0' Then set SubSection = 'One'
Where ID = '10F9F862-4723-4105-A8E9-000017097EBE' Then set SubSection = 'Two'
Where ID = '25810E61-8E86-432B-9387-00003048E74D' Then set SubSection = 'Three'

I thought I could do something like what is in this thread SQL Insert Into with Inner Join, but again, I can't see how I would be able to apply it to my situation?

Any help would be greatly appreciated.

EDIT: I apologize if this changes the question too much, but I am trying to update anywhere from 50,000 to 100,000 records and I am trying to optimize the code to Update faster then looping over a single query and updating that way.

Upvotes: 1

Views: 184

Answers (3)

Ed Bangga
Ed Bangga

Reputation: 13006

You can maximize the use of table value constructor available in sql server 2008 and higher versions. It will be easier considering you have multiple values.

update [dbo].[ShiftTimes] 
set SubSection  = t2.SubSection
from 
(values ('9E3C491A-3FE1-47F1-B701-000009C08CD0','One')
        , ('10F9F862-4723-4105-A8E9-000017097EBE','Two')
        , ('25810E61-8E86-432B-9387-00003048E74D','Three'))  
       AS t2 (ID, SubSection) 
where t2.ID = ID

Upvotes: 2

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112612

Since you need to update many rows, I suggest inserting the new values into a temp table together with the ID and then use it as source for the update statement. These are 3 statements.

CREATE TABLE #temp
(
    ID CHAR(36),            -- Choose the same types as in the ShiftTimes table
    NewValue VARCHAR(100)
)

INSERT INTO #temp
  (ID, NewValue)
VALUES
  ('9E3C491A-3FE1-47F1-B701-000009C08CD0', 'One'),
  ('10F9F862-4723-4105-A8E9-000017097EBE', 'Two'),
  ('25810E61-8E86-432B-9387-00003048E74D', 'Three')

UPDATE
    [dbo].[ShiftTimes]
SET
    ShiftTimes.SubSection = T.NewValue
FROM
    [dbo].[ShiftTimes] S
    INNER JOIN #temp T
        ON S.ID = T.ID;

The second statement is the big one with > 50,000 rows and each Id/value-pair appears only once.

Upvotes: 4

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522254

Use a CASE expression:

UPDATE [dbo].[ShiftTimes]
SET
    SubSection = CASE ID WHEN '9E3C491A-3FE1-47F1-B701-000009C08CD0' THEN 'One'
                         WHEN '10F9F862-4723-4105-A8E9-000017097EBE' THEN 'Two'
                         WHEN '25810E61-8E86-432B-9387-00003048E74D' THEN 'Three' END
WHERE
    ID IN ('9E3C491A-3FE1-47F1-B701-000009C08CD0',
           '10F9F862-4723-4105-A8E9-000017097EBE',
           '25810E61-8E86-432B-9387-00003048E74D');

Upvotes: 2

Related Questions