471,575 Members | 1,532 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,575 software developers and data experts.

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 2727
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
reply views Thread by XIAOLAOHU | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by lumer26 | last post: by
reply views Thread by Vinnie | last post: by
reply views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.