Multiple UPDATE queries-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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).