SQL 使用 CASE 语句更新列中的行

SQL update rows in column using CASE statement

提问by Will Weld

I have two tables, Users and #TempTable (which is a subset of Users). I would like to update a column, IsActive, in the Users table. I would like to set IsActive = 1 if a user that is in #TempTable is also in the Users table, and set IsActive = 0 otherwise.

我有两个表，用户和#TempTable（这是用户的一个子集）。我想更新用户表中的列 IsActive。如果 #TempTable 中的用户也在用户表中，我想设置 IsActive = 1，否则设置 IsActive = 0。

Getting the users from Users that are NOT in #TempTable (IsActive should be set to 0 for these users):

从不在 #TempTable 中的用户获取用户（这些用户的 IsActive 应设置为 0）：

-- (Users \ #TempTable) U (#TempTable \ Users)
SELECT  u.UserName
FROM    Users u 
WHERE   (u.UserName) NOT IN 
    (SELECT t.[User Name] FROM #TempTable t) 
UNION ALL 
SELECT  t.[User Name] 
FROM    #TempTable t
WHERE   (t.[User Name]) NOT IN 
    (SELECT u.UserName FROM Users u)

Let's call this the ResultSet. I would appreciate some help with my UPDATE statement. What I'd like to be able to do is:

我们称其为结果集。我会很感激我的 UPDATE 语句的一些帮助。我希望能够做的是：

UPDATE Users
SET IsActive = (CASE WHEN User.UserName IN ResultSet THEN 0 ELSE 1 END) 

without having to write out the CASE WHEN for each User.UserName. Thanks in advance!

无需为每个 User.UserName 写出 CASE WHEN。提前致谢！

回答by Hogan

You can use a join in the UPDATE statement.

您可以在 UPDATE 语句中使用连接。

UPDATE Users
SET Users.Active = CASE WHEN T.UserName is null THEN 0 ELSE 1 END 
FROM Users AS U
LEFT JOIN #TempTable AS T ON U.UserName = T.UserName

Notes :

注意事项：

• You could also use a sub-query but that would be much slower (order of n squared not order of n). For a small number of users this would not matter.

• I did nottest so I could have a typo / bug in the code above.

• 您也可以使用子查询，但这会慢得多（n 平方的顺序而不是 n 的顺序）。对于少数用户来说，这无关紧要。

• 我并没有测试，所以我可以在上面的代码输入错误/缺陷。

---

---

Based on crazy comments about how this would not work I implemented a fiddle.

基于关于这将如何行不通的疯狂评论，我实现了一个小提琴。

Enjoy it working:

享受它的工作：

http://sqlfiddle.com/#!6/25235/3

http://sqlfiddle.com/#!6/25235/3