October 23, 2008 at 10:48 am
Hi please help me
i was trying to update using while loop
Here is my code ....
declare @y char(3)
declare @count int
set @y = 'no'
set @count = 1
select @y = ind from idind
where i = @count
while (@count = 6)
begin
if (@y = 'no')
begin
update idind
set ind ='yes'
where i = @count
@count = @count + 1
print @count
end
else
@count = @count + 1
print @count
end
my table is
id ind i
---------------------
123456no 1
123456yes2
123456yes3
123456no 4
123456yes5
actually i created an identity column lo update using a while statement. But this gives me an error
saying
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near '@count'.
Msg 102, Level 15, State 1, Line 25
Incorrect syntax near '@count'.
October 23, 2008 at 10:50 am
Try replacing "@count = @count + 1" with "SET @count = @count + 1"
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 23, 2008 at 11:09 am
Thanks for the quick answer
October 24, 2008 at 1:11 am
[font="Verdana"]
tinku.reddy (10/23/2008)
Hi please help mei was trying to update using while loop
Here is my code ....
declare @y char(3)
declare @count int
set @y = 'no'
set @count = 1
select @y = ind from idind
where i = @count
while (@count = 6)
begin
if (@y = 'no')
begin
update idind
set ind ='yes'
where i = @count
@count = @count + 1
print @count
end
else
@count = @count + 1
print @count
end
my table is
id ind i
---------------------
123456no 1
123456yes2
123456yes3
123456no 4
123456yes5
actually i created an identity column lo update using a while statement. But this gives me an error
saying
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near '@count'.
Msg 102, Level 15, State 1, Line 25
Incorrect syntax near '@count'.
Better approch...
Update myTableSet ind = Case When ind = 'No' Then 'Yes' End
[/font]
MH-09-AM-8694
October 26, 2008 at 9:31 pm
tinku.reddy (10/23/2008)
Hi please help mei was trying to update using while loop
Here is my code ....
declare @y char(3)
declare @count int
set @y = 'no'
set @count = 1
select @y = ind from idind
where i = @count
while (@count = 6)
begin
if (@y = 'no')
begin
update idind
set ind ='yes'
where i = @count
@count = @count + 1
print @count
end
else
@count = @count + 1
print @count
end
You do not need a loop for this at all.
Keep in mind that SQL is SET-BASED, which means it operates on SETS of data.
SQL is NOT a procedural language!!
You can think of SELECT, UPDATE, & DELETE as built-in loops.
You do not need to define your own loops.
The correct way to achieve your results is by using ONE SQL statement.
If you are trying to update all rows to 'yes', which is what it looks like based on your data.
Try the following:
UPDATE MyTable
SET ind = 'yes'
If you are trying to only update the rows with 'no' to 'yes', then use Mahesh's solution.
October 28, 2008 at 5:19 pm
I think u shud say
select @count= @count+1
October 29, 2008 at 7:21 am
I'm having a hard time figuring out what you want to do.
If you want all the rows to have ind=YES:
UPDATE MyTable
SET ind = 'YES'
If you want to update only the rows where ind=NO to YES:
UPDATE MyTable
SET ind = 'YES'
WHERE ind = 'NO'
(Generally, I try not to update the ones that are already right.)
TSQL is set based. UPDATE, SELECT and DELETE act on ALL the rows that meet the condition in the WHERE clause. (If there is no WHERE clause, they act on ALL the rows in the table.)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply