Can you help me? - 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 2000 Forums

Transact-SQL (2000)

Can you help me?

Author

Topic

sahu74
Posting Yak Master

100 Posts

Posted - 2002-10-31 : 12:39:44

I have a batch query in the format below. This results in the query running through all years from 1996 to 2002. What I need is a way to run the query for some selected years. The years I want can be got from another table 'YrNum' using a simple SELECT statement.

I would appreciate any help for this.

**********
declare @Yr int

set @Yr = 1996
while @Yr <=2002
Begin
update tableXYZ
set HCUG =(SELECT COUNT(*)
FROM tableABC
WHERE AcademicYear =@Yr )
where year = @Yr
set @Yr = @Yr + 1
END

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-31 : 12:58:54

Try this

UPDATE [tableXYZ]
SET [HCUG] = (SELECT COUNT(*) FROM tableABC INNER JOIN YrNum ON tableABC.AcademicYear = YrNum.AcademicYear And tableABC.AcademicYear = tableXYZ.year)

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-10-31 : 13:00:00

How bout a change to your while statement.

while @yr <= 2002 and @yr in (select YrNum from simpleselectTable)

You could also get rid of the interative loop... Setbased would be fast.

-----------------------
SQL isn't just a hobby, It's an addiction

sahu74
Posting Yak Master

100 Posts

Posted - 2002-10-31 : 13:48:38

Thank you so much for the quick solution. Really appreciate it.
pks.

quote:

How bout a change to your while statement.

while @yr <= 2002 and @yr in (select YrNum from simpleselectTable)

You could also get rid of the interative loop... Setbased would be fast.

-----------------------
SQL isn't just a hobby, It's an addiction

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources