具有多个 SET 和 WHERE 的 SQL 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6446250/
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
SQL Statement with multiple SETs and WHEREs
提问by ProgramNov
I am wondering if this is a valid query:
我想知道这是否是一个有效的查询：
UPDATE table
SET ID = 111111259
WHERE ID = 2555
AND SET ID = 111111261
WHERE ID = 2724
AND SET ID = 111111263
WHERE ID = 2021
AND SET ID = 111111264
WHERE ID = 2017
回答by Brandon Boone
NO!
不！
You'll need to handle those individually
您需要单独处理这些
Update [table]
Set ID = 111111259
WHERE ID = 2555
Update [table]
Set ID = 111111261
WHERE ID = 2724
--...
回答by niktrs
Best option is multiple updates.
最好的选择是多次更新。
Alternatively you can do the following but is NOTrecommended:
或者，您可以执行以下操作，但不建议这样做：
UPDATE table
SET ID = CASE WHEN ID = 2555 THEN 111111259
WHEN ID = 2724 THEN 111111261
WHEN ID = 2021 THEN 111111263
WHEN ID = 2017 THEN 111111264
END
WHERE ID IN (2555,2724,2021,2017)
回答by Ahmad
No. That is not a valid query. You can only have one SET statement, with multiple fields, however, one WHERE clause as well
不，这不是一个有效的查询。您只能有一个包含多个字段的 SET 语句，但也可以有一个 WHERE 子句
update table1 set field1=value1, field2=value2, field3=value3 where filed4=value5
回答by Kyle Undefined
Nope, this is how you do it:
不，这就是你的做法：
UPDATE table SET ID = 111111259 WHERE ID = 2555
UPDATE table SET ID = 111111261 WHERE ID = 2724
UPDATE table SET ID = 111111263 WHERE ID = 2021
UPDATE table SET ID = 111111264 WHERE ID = 2017
回答by Limey
No, you would need to create a seperate query for each update.
不，您需要为每个更新创建一个单独的查询。
回答by BradC
No. You'll have to do separate updates:
不。您必须进行单独的更新：
UPDATE table
SET ID = 111111259
WHERE ID = 2555
UPDATE table
SET ID = 111111261
WHERE ID = 2724
UPDATE table
SET ID = 111111263
WHERE ID = 2021
UPDATE table
SET ID = 111111264
WHERE ID = 2017
回答by PRacicot
since sql those all the lines you want it to do, I would do you're code like thise
因为 sql 那些你想要它做的所有行，我会做你这样的代码
Inside you Sql management too do execute query and this should work.
在您的 Sql 管理中也执行查询，这应该可以工作。
UPDATE table
SET ID = 111111259 WHERE ID = 2555
UPDATE table
SET ID = 111111261 WHERE ID = 2724
UPDATE table
SET ID = 111111263 WHERE ID = 2021
UPDATE table
SET ID = 111111264 WHERE ID = 2017
回答by Abdelghafour Ennahid
You can also use case then like this:
你也可以像这样使用 case：
UPDATE table
SET ID = case
when ID = 2555 then 111111259
when ID = 2724 then 111111261
when ID = 2021 then 111111263
when ID = 2017 then 111111264
else ID
end
回答by pr0grammaniac
No, you need to handle every statement separately..
不，您需要单独处理每个语句。
UPDATE table1
Statement1;
UPDATE table 1
Statement2;
And so on
等等
回答by Paul Vernon
You could do this
你可以这样做
WITH V(A,B) AS (VALUES
(2555,111111259)
,(2724,111111261)
,(2021,111111263)
,(2017,111111264)
)
SELECT COUNT(*) FROM NEW TABLE (
UPDATE table
SET id = (SELECT B FROM V WHERE ID = A)
WHERE EXISTS (SELECT B FROM V WHERE ID = A)
)
Note, does not works on column organized tables. Use MERGEin that case
请注意，不适用于按列组织的表。MERGE在这种情况下使用
