Multiple UPDATE queries

Question

I have a few tables in my database that I need to update.

Unfortunately, this data has never captured in the past and needs to be done now.

The table I have currently has NULL in COL2 and COL3, but they have unique keys in COL1 (obviously).

The basic layout of the tables is like this

ID     desc   dateCreated  wRef    tRef
---------------------------------------
T06    url    1/08/2016    NULL    NULL
T07    url    1/09/2016    NULL    NULL
T04    url    11/10/2016   NULL    NULL

The values I'm looking at updating are the wRef and the tRef. These values are all unique.

I have been using the below code to update each line. I have this duplicated 1000s of times as there are 1000s of IDs to update.

UPDATE [production].[dbo].[jobsTable] 
SET [wRef] = '0004-01', [tRef] = 'T00-07' 
WHERE [ID] = 'U19695L'

UPDATE [production].[dbo].[jobsTable] 
SET [wRef] = '0005-01', [tRef] = 'T10-04' 
WHERE [ID] = 'U16129L'

UPDATE [production].[dbo].[jobsTable] 
SET [wRef] = '0010-01', [tRef] = 'T01-06' 
WHERE [ID] = 'U17175L'

I'm a SQL newbie - is there an easier or cleaner way to do this?

Thanks in advance.

You _could_ write a single update with a big ugly `CASE` expression, but I would leave your code as is. — Tim Biegeleisen, Oct 13 '22 at 02:44
You can check this [case-when-update](https://stackoverflow.com/questions/15766102/i-want-to-use-case-statement-to-update-some-records-in-sql-server-2005) — flyingfox, Oct 13 '22 at 02:45
What's the source for these statements? Do you have a CSV file with the source data in, or something similar? Based on what you've posted there's no better solution. Looking at where the data comes from might suggest something. — Tangentially Perpendicular, Oct 13 '22 at 02:45

score 0 · Answer 1 · answered Oct 13 '22 at 02:52

0

A simple solution using SQL CASE for you

UPDATE
[production].[dbo].[jobsTable]
SET 
    [wRef] =          
          CASE  
            WHEN [ID] = 'U19695L' THEN '0004-01' 
            WHEN [ID] = 'U16129L' THEN '0005-01'
            WHEN [ID] = 'U17175L'  THEN '0010-01'
            ELSE [wRef]
          END ,
    [tRef] = 
           CASE  
                    WHEN [ID] = 'U19695L' THEN 'T00-07'
                    WHEN [ID] = 'U16129L' THEN 'T10-04'
                    WHEN [ID] = 'U17175L'  THEN 'T01-06'
                    ELSE [tRef]
           END
WHERE [ID] IN( 'U17175L','U16129L','U17175L' )

answered Oct 13 '22 at 02:52

flyingfox

13,414
3
24
39

Might work for three rows - but imagine having hundreds, thousands or even more .... gets extremely unwieldy very quickly. – marc_s Oct 13 '22 at 04:07
@marc_s Agree with you,so if there are massive records,so it would be better to use a temp table to store it,as seanb's answer – flyingfox Oct 13 '22 at 06:13

seanb · Answer 2 · 2022-10-13T04:54:56.190

Personally, I'd create either a temporary table, or a standard table, with the relevant data you want updated, then do a single update statement. It's a lot cleaner/easier to manage, and the single update probably performs much faster as well.

For example - using a temporary table with the data to be updated:

CREATE TABLE #jobsUpdates (ID nvarchar(10) PRIMARY KEY, wRef nvarchar(10), tRef nvarchar(10));

INSERT INTO #jobsUpdates (ID, wRef, tRef) VALUES
('U19695L', '0004-01', 'T00-07'),
('U16129L', '0005-01', 'T10-04'),
('U17175L', '0010-01', 'T01-06');

UPDATE  jT
SET     [wRef] = jU.[wRef], 
        [tRef] = jU.[tRef]
FROM    [production].[dbo].[jobsTable] jT
        INNER JOIN #jobsUpdates jU ON jT.ID = jU.Id

It's even simpler if you just have the data already in a table you can access and edit data directly (for example - create the table, copy-and-paste in the data from Excel, then do the one update statement joining jobsTable with the source data).

Multiple UPDATE queries

2 Answers2