Update multiple rows with sub query

Question

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 figure out the solution... I have a feeling its very obvious.

Your help is appreciated.

Thanks.

onedaywhen · Accepted Answer · 2011-05-13 07:15:46Z

3

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';

answered May 13, 2011 at 7:15

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stan Over a year ago

Thanks for your help. Its on SQL2005 so I don't have Merge available.

zerkms · Accepted Answer · 2011-05-13 03:32:20Z

3

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

answered May 13, 2011 at 3:32

zerkms

256k73 gold badges447 silver badges551 bronze badges

Collectives™ on Stack Overflow

Update multiple rows with sub query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related