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.
| Author |
Topic |
|
sahu74
Posting Yak Master
100 Posts |
 Posted - 2002-11-02 : 16:40:21
|
| I had posted this question earlier...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 I got this as one of the suggestions from M.E.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. I tried this and thought it was working fine. But now I have a problem. When there is a break in the sequence of numbers (say the yrNum table has years 1999, 2000, 2002), i.e 2001 is not there, then the loop seems to break. What I mean is that the query updates for yr 1999, 2000 only and not for 2002. Could you please suggest some solution. |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-11-02 : 23:28:12
|
An OR instead of AND quote:
while @yr <= 2002 and @yr in (select YrNum from simpleselectTable)
would definetely solve the problem with breaking out of the loop. Would it botch what you are trying to do though?Sarah Berger MCSD |
 |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2002-11-03 : 22:10:27
|
| CREATE TABLE YearList (yr INTEGER NOT NULL PRIMARY KEY);Load that table and then :UPDATE TableXYZ SET hcug =(SELECT COUNT(*) FROM TableABC WHERE academic_year IN (SELECT yr FROM YearList)); --CELKO--Joe Celko, SQL Guru |
|
|
|
|
|
|
Some more help please...