469,077 Members | 1,392 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,077 developers. It's quick & easy.

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.
Feb 15 '07 #1
2 2663
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
Feb 15 '07 #2
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.
Feb 15 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Roy Adams | last post: by
12 posts views Thread by Peter Proost | last post: by
10 posts views Thread by chimambo | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.