Geek Answers Handbook

SQL update rows in a column using a CASE statement

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

Getting users from users who are NOT in #TempTable (these users must be set to 0 for IsActive):

-- (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 me call it ResultSet. I would appreciate help in my UPDATE statement. What I would like to do is:

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

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

+4
source share
1 answer

You can use the connection in an 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

Notes:

  • You can also use a subquery, but it will be much slower (order of n squares is not order n). For a small number of users, this does not matter.

  • I did not test so that I had a typo / error in the code above .

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

Enjoy the work:

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

+5
source

More articles:


All Articles