在 SQL 中更新多条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5778140/
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 records in SQL
提问by hhh3112
How can i update multiple records in a single statement like this with SQL?:
如何使用 SQL 在这样的单个语句中更新多条记录？：
UPDATE records
SET name='abc' where id=3,
SET name='def' where id=1
回答by Jose Rui Santos
You can simply combine an update with a case statement such
您可以简单地将更新与 case 语句结合起来，例如
UPDATE records
SET name =
CASE
WHEN id = 3 THEN 'abc'
WHEN id = 1 THEN 'def'
ELSE name
END
回答by Guffa
For just a few records, you could use:
对于一些记录，您可以使用：
update records
set name = case id
when 1 then 'def'
when 3 then 'abc'
end
where id in (1, 3)
A bit more flexible is to create a result that you can join into the update:
更灵活的是创建一个可以加入更新的结果：
update r
set name = x.name
from records r
inner join (
select id = 1, name = 'abc' union all
select 3, 'def' union all
select 4, 'qwe' union all
select 6, 'rty'
) x on x.id = r.id
回答by Martin Smith
;WITH vals(id, name)
AS (SELECT 3,'abc'
UNION ALL
SELECT 1,'def')
UPDATE r
SET name = vals.name
FROM records r
JOIN vals
ON vals.id = r.id
回答by onedaywhen
Standard SQL:2003 syntax (works on SQL Server 2008 onwards):
标准 SQL:2003 语法（适用于 SQL Server 2008 以上）：
MERGE INTO records
USING (
VALUES (1, 'def'),
(3, 'abc')
) AS T (id, name)
ON records.id = T.id
WHEN MATCHED THEN
UPDATE
SET name = T.name;
Note that NAMEand RECORDSare SQL reserved words.
请注意，NAME和RECORDS是 SQL 保留字。
