Update multiple records with differing where and set values?

Question

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.

Is this just an exercise? I really don't see the reason for making a complex query unless you need to update `N` number of rows in a loop using dynamic SQL. If that is not the case why not just run 3 statements? — T.S., Dec 26 '19 at 02:37
@T.S. This is just a small example of what I need to show what I am doing. In reality I will need to update anywhere from 50,000 to 100,000 records and running line by line is just so slow. — KyloRen, Dec 26 '19 at 02:38
If `WHERE ID = '9E3C491A-3FE1-47F1-B701-000009C08CD0'` affect many records, I really don't see how you will gain much by running single statement. You will still have search performed on table — T.S., Dec 26 '19 at 02:44
@T.S Not if ID is the primary key, this ensures only one record will be affected. — KyloRen, Dec 26 '19 at 02:49
I guess, the question then, how you are going to identify, which id is shift "one", "two", or "three" — T.S., Dec 26 '19 at 02:56
@T.S. Absolutely correct, but that issue is also sorted out. I just needed a way to optimise the update statement to make it faster. — KyloRen, Dec 26 '19 at 03:24

Olivier Jacot-Descombes · Accepted Answer · 2019-12-26T02:57:58.413

4

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.

edited Dec 26 '19 at 02:57

answered Dec 26 '19 at 02:52

Olivier Jacot-Descombes

104,806
13
138
188

Hey, thanks so much for the code. Got it working, but it will only take 60 pairs of data (ID, NewValue) ie, only update 60 records at a time. Is there a workaround for this? Or is that the max SQL can handle? – KyloRen Dec 26 '19 at 11:11
According to [Table Value Constructor (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15), the limit is 1000 rows. Don't know why you hit a limit at 60. You will have to insert in batches. – Olivier Jacot-Descombes Dec 26 '19 at 16:11

score 2 · Answer 2 · answered Dec 26 '19 at 02:31

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');

Thanks for the help, it is very much appreciated. – KyloRen Dec 26 '19 at 23:02 — KyloRen, Dec 26 '19 at 23:02

score 2 · Answer 3 · answered Dec 26 '19 at 02:59

2

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

answered Dec 26 '19 at 02:59

Ed Bangga

12,879
4
16
30

Hey, thanks for the answer but, I am getting ambiguous name for `ID`. Looks like the compiler is showing it as `VARCHAR` instead of a `uniqueidentifier`. How do I get around this? – KyloRen Dec 26 '19 at 03:37
@KyloRen, you can change tvc columns as `(IDt, Subsection)`, then `t2.IDt = ID`. – Ed Bangga Dec 26 '19 at 03:38

Update multiple records with differing where and set values?

3 Answers3