Updating Multiple Records Based On Records Returned By A Select Statement

Hi everyone, I feel as this is a very simple question, but I cannot find any information pertaining to it.

I want to be able to update a set of records that were returned through a select statement. I know that T-SQL does not allow for arrays, so I guess my problem is finding a placeholder for the returned items from the SELECT statement, then doing a UPDATE on those records.

DECLARE @OnCallUser nvarchar(30);
SELECT @OnCallUser = (SELECT _STAFF_ FROM _SMDBA_.ONCALL_ WHERE _GROUP_=1040 AND TierOne=1);
UPDATE _SMDBA_.ONCALL_ SET TierOne=1 WHERE _STAFF_=@OnCallUser AND _GROUP_=1047;

The problem with this code is that I only return one _STAFF_. If more than 1 record returns from the SELECT statement, this fails.

Any help would be appreciated. Thanks.

re: Updating Multiple Records Based On Records Returned By A Select Statement

try it this way:

UPDATE _SMDBA_.ONCALL_ SET TierOne=1
WHERE _STAFF_ in (SELECT _STAFF_ FROM _SMDBA_.ONCALL_ WHERE _GROUP_=1040 AND TierOne=1)
AND _GROUP_=1047

re: Updating Multiple Records Based On Records Returned By A Select Statement

I was just looking at the IN function call a second ago, but figured it wouldn't work in this instance. But i was wrong.

Thanks mbrazier.