Regarding a while loop

  • 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'.

  • Try replacing "@count = @count + 1" with "SET @count = @count + 1"



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    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]

  • Thanks for the quick answer

  • [font="Verdana"]

    tinku.reddy (10/23/2008)


    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'.

    Better approch...

    Update myTableSet ind = Case When ind = 'No' Then 'Yes' End

    [/font]

    MH-09-AM-8694

  • tinku.reddy (10/23/2008)


    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

    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.

  • I think u shud say

    select @count= @count+1

  • 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