在 SQL 中更新具有不同值的多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4374285/
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
Update multiple rows with different values in SQL
提问by Bob
I have a table like this:
我有一张这样的表：
SKU Size
A 10
B 10
C 10
D 10
E 10
F 10
G 10
I want to change it to:
我想把它改成：
SKU Size
A 20
B 10
C 30
D 10
E 80
F 10
G 60
I have more than 3000 rows of records to update. How can I do that with SQL update command ?
我有超过 3000 行记录要更新。我怎么能用 SQL 更新命令做到这一点？
回答by Damien_The_Unbeliever
UPDATE T
SET Size = CASE SKU
WHEN 'A' THEN 20
WHEN 'B' THEN 10
WHEN 'C' THEN 30
WHEN ...
END
Or there may be a formula for calculating the size, but you've failed to give it in your question (Or we may have to switch to a more complex CASE expression, but again, too little detail in the question).
或者可能有一个计算大小的公式，但你没有在你的问题中给出它（或者我们可能不得不切换到一个更复杂的 CASE 表达式，但同样，问题中的细节太少）。
回答by Jonathan Leffler
Create a table with the mapping of SKU to new size; update the master table from that.
创建一个表，将 SKU 映射到新的大小；从中更新主表。
Many dialects of SQL have a notation for doing updates via joined tables. Some do not. This will work where there is no such notation:
许多 SQL 方言都有通过连接表进行更新的符号。有些没有。这将在没有这样的符号的情况下工作：
CREATE TABLE SKU_Size_Map
(
SKU CHAR(16) NOT NULL,
Size INTEGER NOT NULL
);
...Populate this table with the SKU values to be set...
...You must have such a list...
UPDATE MasterTable
SET Size = (SELECT Size FROM SKU_Size_Map
WHERE MasterTable.SKU = SKU_Size_Map.Size)
WHERE SKU IN (SELECT SKU FROM SKU_Size_Map);
The main WHERE condition is need to avoid setting the size to null where there is no matching row.
主要的 WHERE 条件需要避免在没有匹配行的情况下将大小设置为 null。
You can probably also do it with a MERGE statement. But the key insight for any of these notations is that you need a table to do the mapping between SKU and size. You either need a table or you need an algorithm, and the sample data doesn't suggest an algorithm.
您可能也可以使用 MERGE 语句来做到这一点。但是对于这些符号中的任何一个的关键见解是您需要一个表来进行 SKU 和大小之间的映射。您要么需要表格，要么需要算法，而样本数据并未建议算法。
回答by Pranay Rana
Make use of OpenXMLto resolve your issue
使用OpenXML解决您的问题
example
例子
declare @i int
exec sp_xml_preparedocument @i output,
'<mydata>
<test xmlID="3" xmlData="blah blah blah"/>
<test xmlID="1" xmlData="blah"/>
</mydata>'
insert into test
select xmlID, xmlData
from OpenXml(@i, 'mydata/test')
with (xmlID int, xmlData nvarchar(30))
where xmlID not in (select xmlID from test)
update test
set test.xmlData = ox.xmlData
from OpenXml(@i, 'mydata/test')
with (xmlID int, xmlData nvarchar(30)) ox
where test.xmlID = ox.xmlID
exec sp_xml_removedocument @i
回答by Yves M.
Just do...
做就是了...
UPDATE [yourTable] SET Size = 20 WHERE SKU = 'A'
And do this for all values you want to change...
并对您要更改的所有值执行此操作...
回答by Christian Severin
Well, if you don't have a formula to calculate your Sizes, and you don't have a file or an Excel sheet with the data that you can massage into your table, you'll just have to get some luckless intern to type something like
好吧，如果您没有计算Sizes的公式，并且您没有可以将数据添加到表格中的文件或 Excel 表格，那么您只需要找一些不幸的实习生来输入就像是
UPDATE <table> SET Size = <value> WHERE SKU = '<key>'
3000 times.
3000 次。
If you are that intern, I'd suggest giving us a little more information...
如果你是那个实习生，我建议你给我们更多的信息......
回答by bob
Since you wanted to change the whole column, drop that particular column by using this:
由于您想更改整个列，请使用以下命令删除该特定列：
ALTER TABLE table_name
DROP COLUMN column_name;
then create a new column using:
然后使用以下方法创建一个新列：
ALTER TABLE table_name
ADD column_name varchar(80);
