SQL CASE Statement

Maybe knew of it, but hadn’t had reason until now to use a CASE statement in SQL:

UPDATE Devices

SET InUse = (CASE UserId WHEN 3

THEN 1

ELSE 0 END)

WHERE DevGuid = '94F74150-26B0-4396-982C-675A14A66FAD';

In this situation, there are multiple rows with the same device GUID because more than one user may use the same app on the device (but not simultaneously…unless they’re extraordinarily close). What this query does is get all the rows that share the same device GUID, then sets the InUse bit to true (1) if the UserId matches (3). Any row within that set with a different user ID gets its InUse bit set to false (0). This avoids having to make more than one call to the server.