title: How to update column value based on records existing in two different tables?
body: <p>Is there a better way of doing the following? (Instead of having two separate queries) I want to update the UserTypeId column to either '<code>USER1</code>'
  or '<code>USER2</code>' if a record exists in the AdminDetails table. </p>
  
  <pre><code>UPDATE Usernames
  SET UserTypeId = (select Id from UserTypes where code = 'USER1')
  WHERE EXISTS
  (SELECT AdminDetails.Id
  FROM AdminDetails
  WHERE AdminDetails.Id = Usernames.Id)
  
  UPDATE Usernames
  SET UserTypeId = (select Id from UserTypes where code = 'USER2')
  WHERE EXISTS
  (SELECT AdminDetails.Id
  FROM AdminDetails
  WHERE AdminDetails.Id = Usernames.Id)
  </code></pre>
  
  <p>I tried using <code>COUNT()</code> but I got the following error when attempting to do this in one query using an inner join and case statement:</p>
  
  <pre><code>UPDATE  Usernames
  SET     UserTypeId = (CASE WHEN COUNT(AdminDetails.Id) &gt; 0 THEN 'USER1' ELSE 'USER2' END)
  FROM    AdminDetails
          INNER JOIN Usernames AS un
              ON  AdminDetails.Id = un.UserId
  </code></pre>
  
  <p>But it gives the following error: '<strong>An aggregate may not appear in the set list of an UPDATE statement</strong>'. </p>
  
  <p>I  want to do this in one query using case when then else by checking if a record exists. How can I do this?</p>