SQL使用CASE语句更新列中的行 [英] SQL update rows in column using CASE statement

问题描述

我有两个表，Users和#TempTable(是Users的子集).我想在用户"表中更新IsActive列.如果用户#TempTable中的用户也位于Users表中，我想将IsActive设置为1，否则将IsActive设置为0.

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应该设置为0):

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

-- (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)

我们将此称为ResultSet.我希望对我的UPDATE语句有所帮助.我想做的是:

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 Users
SET IsActive = (CASE WHEN User.UserName IN ResultSet THEN 0 ELSE 1 END) 

而不必为每个User.UserName写出CASE WHEN.预先感谢！

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

推荐答案

您可以在UPDATE语句中使用联接.

You can use a join in the UPDATE statement.

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

注意:

• 您还可以使用子查询，但这会慢很多(n阶的平方而不是n阶).对于少数用户而言，这没关系.

• 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.

我没有没有测试，所以我在上面的代码中可能有错别字/错误.

基于关于此操作不可行的疯狂评论，我实施了一个小提琴.

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

享受它的工作:

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

这篇关于SQL使用CASE语句更新列中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！