Multiple SQL Update Statements in single query

问题:

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

回答1:

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

回答2:

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

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

回答3:

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'

回答4:

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;

回答5:

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.

回答6:

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.