Looping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Looping

Author

Topic

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-04-05 : 11:27:09

I have a part of a query where I think I need to loop, but I'm not sure how to do that.
Here is my query:

if (select r.regionid from region r
inner join franchiseregionvenue frv on r.regionid = frv.regionid
inner join siteregionvenue srv on frv.franchiseregionvenueid = srv.franchiseregionvenueid
inner join business b on srv.businessid = b.businessid and b.deletedate is null
inner join address a on b.businessid = a.businessid and a.deletedate is null
where postalcode like convert(varchar(10), @postalcodename) + '%')
!=
(select r.regionid from region r
inner join regionpostalcoderef rp on r.regionid = rp.regionid
where postalcode = @postalcodename)
begin

update regionpostalcoderef set
regionid = @regionid,
postalcode = @postalcodename,
lastupdatedate = getDate(),
lastupdateloginid = @loginid,
lastupdatesql = 'RegionPostalCode_InsertUpdate'
where
regionpostalcoderefid = @regionpostalcoderefid
end

The Problem is the first select statement returns multiple results and I want to update one at a time, Any Ideas?

X002548
Not Just a Number

15586 Posts

Posted - 2007-04-05 : 11:28:58

You need to think in set based terms...

Combine the 2 selects into 1 and use that in an update statement

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-04-05 : 11:36:22

I forgot part of my query

here is the query

if (select r.regionid from region r
inner join franchiseregionvenue frv on r.regionid = frv.regionid
inner join siteregionvenue srv on frv.franchiseregionvenueid = srv.franchiseregionvenueid
inner join business b on srv.businessid = b.businessid and b.deletedate is null
inner join address a on b.businessid = a.businessid and a.deletedate is null
where postalcode like convert(varchar(10), @postalcodename) + '%')

!=
(select r.regionid from region r
inner join regionpostalcoderef rp on r.regionid = rp.regionid
where postalcode = @postalcodename)
begin
update regionpostalcoderef set
regionid = @regionid,
postalcode = @postalcodename,
lastupdatedate = getDate(),
lastupdateloginid = @loginid,
lastupdatesql = 'RegionPostalCode_InsertUpdate'
where
regionpostalcoderefid = @regionpostalcoderefid
end
begin

update siteregionvenue set
regionsubid = @regionsubid,
lastupdatedate = getDate(),
lastupdateloginid = @loginid,
lastupdatesql = 'RegionPostalCode_InsertUpdate'
where
siteregionvenueid = (select siteregionvenueid from siteregionvenue srv
inner join address a on srv.businessid = a.businessid
where postalcode like @postalcodename)
end

I'm really not sure what you mean.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources