单个查询中的多个 SQL 更新语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9162671/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multiple SQL Update Statements in single query
提问by Erick Ely
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.
我的情况是我不得不更新我的数据库中的大约 12,000 个项目。每一行都需要镜像我之前制作的一个 excel 文件。我已经制作了创建每行 SQL 语句的文件，但我不确定是否可以在单个查询中运行每一行。
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
先感谢您
采纳答案by Bassam Mehanni
Yes, you could add all the single-line-Update-statements in one query like you are doing.
是的，您可以像现在一样在一个查询中添加所有单行更新语句。
回答by Icarus
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.
除此之外，运行多个更新是要走的路。
回答by F43G4N
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.
我认为最好的方法是将 Excel 工作表导入到 SQL 数据库中的表中。从那里您可以使用连接为所有 12,000 个项目创建单个更新语句。
For information on how to import Excel sheet into SQL: http://msdn.microsoft.com/en-us/library/ms141209.aspx
有关如何将 Excel 工作表导入 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'
回答by onedaywhen
Take a look at MERGEe.g. something like:
看看MERGE例如：
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.
...但是以这种方式使用表值构造函数不会扩展到 12,000 行！因此，首先将数据从 Excel 复制到服务器上的表，然后使用该表作为源，例如
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;
回答by Arka Kahali
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
您可以使用 Excel 中的 concatenate 函数来构建查询，您需要做的就是构建一个更新查询并拖动其余查询
concatenate("Update set =",,",where = ",,";")
concatenate("更新集=",,",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.
使用上述格式并将单元格拖到末尾或双击右下角以自动填充更新语句。我相信这是最短的方法，并在一个 Go 中运行它。
回答by N West
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.
如果您有大量数据要更新，最好将 excel 文件作为表格加载到数据库中，然后根据加载的表格中的数据更新您的表格。
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.
如果您使用的是 SQL 服务器，则可以使用 SSIS 快速加载文件。
