October 6, 2011 at 1:14 pm
A B A+B
== == ===
123
13NULL
14NULL
15NULL
16NULL
17NULL
123
13NULL
14NULL
15NULL
16NULL
17NULL
i have this table i want to update the A+B as as sum of column A nd B nd i want to do it through loop
m making a mistake nd making loop infinite please help me to fix this problem ...
my Query
=======
declare @temp1 int ,@temp2 int
while (1=1)
begin
set rowcount 1
select @temp1= A ,
@temp2= B from #test
if @@rowcount=0
begin
break
end
set rowcount 0
update #test
set c=(A+B)
where A=@temp1
and B=@temp2
end
set rowcount 0
select * from #test
October 6, 2011 at 1:33 pm
faruk.arshad (10/6/2011)
A B A+B== == ===
123
13NULL
14NULL
15NULL
16NULL
17NULL
123
13NULL
14NULL
15NULL
16NULL
17NULL
i have this table i want to update the A+B as as sum of column A nd B nd i want to do it through loop
m making a mistake nd making loop infinite please help me to fix this problem ...
my Query
=======
declare @temp1 int ,@temp2 int
while (1=1)
begin
set rowcount 1
select @temp1= A ,
@temp2= B from #test
if @@rowcount=0
begin
break
end
set rowcount 0
update #test
set c=(A+B)
where A=@temp1
and B=@temp2
end
set rowcount 0
select * from #test
Actually the mistake is using a loop for this!!!!
this is a simple one line update statement
Update table set Col3 = col1 + col2
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 6, 2011 at 1:37 pm
If the professor who assigned this is looking for a loop, then use a cursor for it. That's a "loop" in T-SQL.
If it isn't homework, skip the loop and do it all at once.
If you need a loop and can't use a cursor (professors get touchy about these things), try using Update Top(1) and using a Where clause to filter out rows that have already been done, instead of setting rowcount to 1.
And again, if it isn't homework/test, skip the loop entirely.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 6, 2011 at 1:38 pm
yes i know tat ..but i want to use loop becoz want clear looping concept in SQL jst pretty mch confuse in it..
October 6, 2011 at 1:39 pm
I'd have to agree with Sean about not needing a loop for this but if I understood your question, what's causing your infinite loop is the "while (1=1)" condition.
_____________________________________________________________________
- Nate
October 6, 2011 at 1:43 pm
faruk.arshad (10/6/2011)
yes i know tat ..but i want to use loop becoz want clear looping concept in SQL jst pretty mch confuse in it..
Just my 2¢ but since you sound like you are fairly new to sql and don't know how to do looping, find ways to accomplish what you want and pretend loops are not an option. Loops are performance killers. To coin a phrase from Jeff Moden, don't think about what you want to do to a row, instead think about what you want to do to a column.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 6, 2011 at 1:45 pm
SSchampion
curser can u write sample query of it ?
October 6, 2011 at 1:46 pm
faruk.arshad (10/6/2011)
yes i know tat ..but i want to use loop becoz want clear looping concept in SQL jst pretty mch confuse in it..
if object_id(N'tempdb..#T') is not null drop table #T;
set nocount on;
select 1 into #T;
while @@rowcount > 0
update top(1) #test
set c = a + b
where c is null;
I'm assuming from your sample code that the A+B column is "c".
That'll do what you want, in a loop. Is it clear enough? Does it help you understand looping in T-SQL?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 6, 2011 at 1:51 pm
faruk.arshad (10/6/2011)
@old handbut i have the inner condition if @@rowcount=0 then break then shouldnt go infinite......
Curser ?? can u write sample query of it ?
Yes you could use a **cough** cursor **cough** to do this but it comes highly NOT recommended. A cursor is just another looping mechanism. The example Gus showed you should clarify your confusion about looping. In the truest sense, looping in t-sql is crutch that is carried over from programming. There are, although rare, legitimate reasons to use a looping structure.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 6, 2011 at 1:53 pm
faruk.arshad (10/6/2011)
@old handbut i have the inner condition if @@rowcount=0 then break then shouldnt go infinite......
Yep, my bad.
_____________________________________________________________________
- Nate
October 6, 2011 at 2:12 pm
RP_DBA (10/6/2011)
faruk.arshad (10/6/2011)
@old handbut i have the inner condition if @@rowcount=0 then break then shouldnt go infinite......
Yep, my bad.
No you were correct. This will never break out of the loop. Assuming there is at least 1 row in #test it will always stay in the loop.
while (1=1)
begin
set rowcount 1
select @temp1= A, @temp2= B from #test
if @@rowcount=0
begin
break
end
set rowcount 0
update #test
set c=(A+B)
where A=@temp1
and B=@temp2
end
@@rowcount will ALWAYS be count of rows in #test and there is nothing that deletes any rows inside the loop. There is no way out.
The only way this loop could have worked is if the select statement had a where clause like
select @temp1= A, @temp2= B from #test where c != a + b
Then it would process RBAR until there were no more rows that had not been updated.
Also, just so you aware, set rowcount is deprecated and will not work in the next release of sql. http://msdn.microsoft.com/en-us/library/ms188774.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 6, 2011 at 2:15 pm
while (1=1)
begin
set rowcount 1
select @temp1= A ,
@temp2= B from #test
if @@rowcount=0
begin
That @@rowcount will never equal zero.
Try this, you'll see why:
DECLARE @a CHAR(1) ;
CREATE TABLE #T (C1 CHAR(1)) ;
INSERT INTO #T
(C1)
VALUES ('a'),
('b') ;
WHILE 1 = 1
BEGIN
SELECT @a = C1
FROM #T ;
IF @@ROWCOUNT = 0
BREAK
ELSE
PRINT @a ;
END ;
PRINT @a ;
It goes into an infinite loop, and you'll have to stop it.
There's nothing in there that would cause the rowcount to go to 0. It just keeps selecting a value into a variable (which sets the rowcount to the number of rows in the table you are selecting from).
It also doesn't step from row to row, it just keeps selecting the same one over and over again.
Also try this:
DECLARE @a CHAR(1), @Rows INT ;
CREATE TABLE #T (C1 CHAR(1)) ;
INSERT INTO #T
(C1)
VALUES ('a'),
('b') ;
WHILE 1 = 1
BEGIN
SELECT @a = C1
FROM #T ;
SET @Rows = @@ROWCOUNT;
IF @Rows = 0
BREAK
ELSE
PRINT @Rows ;
END ;
PRINT @a ;
Since it prints the value in @Rows, which is the @@Rowcount value, you'll see what you end up with.
You have to somehow step through the rows, not just select from a row. That's why my example of a loop has a Where clause that discounts the rows that have already been set. Alternatively, a cursor and the Fetch Next command will step through for you.
If you want details on how to use a cursor for this kind of thing, search online for "t-sql cursor", and you'll find all the documentation on it. They're more complex than can be covered in a post on a forum.
The main thing to know about cursors, however, is AVOID THEM. The second most important thing to know about cursors is, AVOID THEM. The third most important thing to know about cursors is, DON'T USE THEM. Everything else about them comes after that. Seriously. I'm not kidding at all.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply