Update multiple rows with sub query

I have two tables, UserStatus and User.

UserStatus has two fields: Username,Status

User has two fields Username, Deleted

This is the query I'm using

Update users
   set deleted = '1'
where username = (select username
                    from tempDUDPIVOT 
                   where status = 'inactive')

but get the error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I've tried a number of variations but can't fig开发者_开发知识库ure out the solution... I have a feeling its very obvious.

Your help is appreciated.

Thanks.

The UPDATE syntax requires a scalar subquery. Using EXISTS (subquery) bring the parameters of the predicate closer together (i.e. makes the query easier to read and maintain, IMO) e.g.

UPDATE users 
   SET deleted = '1' 
 WHERE EXISTS (
               SELECT *
                 FROM tempDUDPIVOT AS T1
                WHERE T1.status = 'inactive'
                      AND T1.username = users.username
              );

You could also use Standard SQL MERGE if your SQL product supports it (though note the parameters of the predicate further apart here):

MERGE INTO users 
   USING (
          SELECT username
            FROM tempDUDPIVOT
           WHERE status = 'inactive'
         ) AS T1 (username)
      ON T1.username = users.username
WHEN MATCHED THEN 
   UPDATE
      SET users.deleted = '1';

Update users set deleted = '1'
where username IN (select username from tempDUDPIVOT where status = 'inactive')

IN accepts 0..inf values to be returned, and = accepts 1 and only one (not 0 or 42).