Multiple SQL Update Statements in single query

Question

I am in a situation where I am having to update about 12,000 items in my DB. Each row needs to mirror an excel file that I made previously. I have made the file that creates each line of SQL statement, but I am not sure if I can run each line in a single query.

This is an example of what I am trying to do.

UPDATE [STORESQL].[dbo].[RPT_ITM_D] SET F1301='1.29' WHERE F01='0000000000001'
UPDATE [STORESQL].[dbo].[RPT_ITM_D] SET F1301='1.39' WHERE F01='0000000000002'

Will this work, or are there any better options for what I am trying to achieve?

Each item will have a unique value and the column to be changed will have a unique value as well. I don't see how I could make this work with a loop, or any other methods I've found so far. I realize that this might take a long time to process, but time is not an issue.

Thank you in advance

score 30 · Answer 1 · edited Oct 27 '21 at 18:10

30

Something like this is the best you can do:-

UPDATE [STORESQL].[dbo].[RPT_ITM_D] 
SET   F1301 = 
      case  F01 
      when '0000000000001' then '1.29'
      when '0000000000002' then '1.30'
      ELSE F1301

end

Other than that, running multiple updates is the way to go.

edited Oct 27 '21 at 18:10

Tom Clelford

691
6
8

answered Feb 06 '12 at 15:43

Icarus

63,293
14
100
115

6

He is trying to update 12,000 unique values, I don't think Case is a valid option here – Bassam Mehanni Feb 06 '12 at 16:38
@BassamMehanni I understand. I was just pointing out that as far as reducing the # of update statements, using `case` is probably the best he can do. – Icarus Feb 06 '12 at 16:41
1

Just in case my edit doesn't get approved - be careful running this without an ELSE in your CASE statement. Anything that isn't handled will be set to NULL! – Tom Clelford Oct 26 '21 at 16:08

score 13 · Accepted Answer · answered Feb 06 '12 at 15:45

13

Yes, you could add all the single-line-Update-statements in one query like you are doing.

answered Feb 06 '12 at 15:45

Bassam Mehanni

14,796
2
33
41

onedaywhen · Answer 3 · 2012-02-06T16:08:49.180

Take a look at MERGE e.g. something like:

MERGE INTO [STORESQL].[dbo].[RPT_ITM_D] 
   USING (
          VALUES ('1.29', '0000000000001'),
                 ('1.39', '0000000000002')
         ) AS source (F1301, F01)
   ON F01 = source.F01
WHEN MATCHED THEN
   UPDATE
      SET F1301 = source.F1301;

...but using a table value constructor in this way would not scale to 12,000 rows! So look to first copying the data from Excel to a table on the server, then use the table as the source e.g.

MERGE INTO [STORESQL].[dbo].[RPT_ITM_D] 
   USING [STORESQL].[dbo].MyStagingTable AS source
      ON F01 = source.F01
WHEN MATCHED THEN
   UPDATE
      SET F1301 = source.F1301;

score 2 · Answer 4 · answered Feb 06 '12 at 15:48

If you have a significant amount of data to update, it may be advantageous to load the excel file into the database as a table, then update your table based on the data in this loaded table.

   UPDATE RPT_ITM_D
      SET F1301 = NewTable.Value
     FROM RPT_ITM_D INNER JOIN NewTable ON (NewTable.F01 = RPT_ITEM_D.F01);

If you're on SQL server, you could use SSIS to load the file pretty quickly.

score 2 · Answer 5 · answered Feb 06 '12 at 15:51

I think the best way is to import the Excel sheet into a table in your SQL database. From there you could be able to use a join to create a single update statement for all 12,000 items.

For information on how to import Excel sheet into SQL: http://msdn.microsoft.com/en-us/library/ms141209.aspx

The update statement would then look something like this:

UPDATE itemTable
SET F1301 = excelTable.<column with your value>
FROM [STORESQL].[dbo].[RPT_ITM_D] itemTable inner join [STORESQL].[dbo].[importedExcelTableName] excelTable on itemTable.F01 = excelTable.<column with the item code>

If you aren't sure if this would work safely, you can try this query for a single value by simple adding:

WHERE itemTable.F01 = '0000000000001'

score 1 · Answer 6 · answered Feb 13 '14 at 07:20

You can use the concatenate function in Excel to frame a query, All you need to do is to frame a single update query and drag the same for the rest

concatenate("Update set =",,",where = ",,";")

Use the above format and drag the cell till the end or double click on the bottom right corner for Auto fill of the Update statement. I beleive this is the shortest way possible and run it in a single Go.

score 1 · Answer 7 · answered Jun 18 '21 at 12:52

Make a unique constraint on the F01 column and then use an insert statement with an 'ON DUPLICATE KEY UPDATE' statement.

INSERT INTO [STORESQL].[dbo].[RPT_ITM_D] (F01, F1301)
VALUES
  ('0000000000001','1.29'),
  ('0000000000002','1.39')
ON DUPLICATE KEY UPDATE F1301 = VALUES(F1301);

Multiple SQL Update Statements in single query

7 Answers7

Linked