Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2007
    Posts
    29

    Unanswered: SQL Update problem with WHILE ?!

    Hi everybody,

    Im writing an update statement in interactive SQL but i cant get i to work.

    Here is the thing

    table1
    - seq
    - name
    - cat
    - number

    For now the number field is empty but i want to update the number field depending of the value in the cat field.

    if cat field has the value somthing_1 the number should be updated like this for example:

    number field
    10
    11
    12
    13
    etc ..

    If cat field is something_2 then number should start like this:

    40
    41
    42
    etc ..

    Im trying to do a loop with the following SQL, its a start. Im little unsecure how to solve this:

    DECLARE @i int
    DECLARE @counter int
    DECLARE @something_1 int
    DECLARE @something_2 int

    set @i = 1
    set @counter = 1
    set @something_1 = 10
    set @something_1 = 40

    WHILE @i > 1
    BEGIN


    UPDATE table1 SET table1.number = @counter + @counter


    END

    Regards
    Magnus

  2. #2
    Join Date
    Oct 2007
    Posts
    29
    Hi again,

    How do you do a manual counter for a field ? Ive tried this below without any luck, i get syntax errors.

    Code:
    DECLARE @counter int
    SET @counter = 0
    UPDATE custom.table1
    SET @counter = table1.name = @counter+1
    Regards
    Magnus

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Code:
    create table #t1 (seq int, name char(3),cat char(2), number int)
    insert into #t1 select
    1, 'aa', 's1',0  union all select
    2, 'bb', 's2',0  union all select
    3, 'cc', 's1',0  union all select
    4, 'cc', 's3',0  union all select
    5, 'cc', 's1',0  union all select
    6, 'cc', 's2',0
    
    declare @i1 int, @i2 int
    select @i1=10, @i2=40
    update #t1 
    set number=case when cat='s1' then @i1 else @i2 end
    ,@i1=case when cat='s1' then @i1+1 else @i1 end
    ,@i2=case when cat='s2' then @i2+1 else @i2 end
    where cat in ('s1','s2')
    
    select * from #t1
    
    drop table #t1
    _________________________________________________________
    
    Result:
    seq  name cat  number
    ---- ---- ---- ------
       1 aa   s1       10
       2 bb   s2       40
       3 cc   s1       11
       4 cc   s3        0
       5 cc   s1       12
       6 cc   s2       41

  4. #4
    Join Date
    Oct 2007
    Posts
    29
    Thx pdreyer,

    When i run the statement you wrote it works except that the result turns out different

    Result:
    seq name cat number
    ---- ---- ---- ------
    1 aa s1 10
    2 bb s2 40
    3 cc s1 10
    4 cc s3 0
    5 cc s1 10
    6 cc s2 40

    The problem might be when im creating the table. It looks like this.

    Code:
    CREATE TABLE custom.t1 (
    	seq INT PRIMARY KEY NOT NULL DEFAULT AUTOINCREMENT, 
    	name CHAR (20), 
    	cat (16),
    	number int
        );
    INPUT INTO custom.t1 FROM C:\test.txt format ASCII (name, cat, number);
    Another thing is that i cant use
    Code:
    #t1
    , i have to write
    Code:
    custom.t1
    Regards
    Magnus

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    I assumed you are using Sybase ASE.
    Which product & version are you using?
    INPUT INTO ... is not valid ASE syntax

  6. #6
    Join Date
    Oct 2007
    Posts
    29
    pdreyer:

    Adaptive Server Anywhere 9
    Sybase Central 4.3.0.2461

    Im writing the SQL in Interactive SQL version 9.0.2

    / Magnus

  7. #7
    Join Date
    Oct 2007
    Posts
    29
    Here is the result in .jpg file.

    / Magnus
    Attached Thumbnails Attached Thumbnails result.JPG  

  8. #8
    Join Date
    Oct 2007
    Posts
    29
    pdreyer:

    Do you think it has something to do with clustered index ?

    // Magnus

  9. #9
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    No, I think it is the way the SQL Anywhere engine handles it.
    I don't have SQL Anywhere or know much about that product.
    It is as different from ASE as the MS products SQL Server and Access
    You can't expect the one product to behave the same as the other.
    The dbforums website does not have a dedicated forum for SQL Anywhere
    Maybe try the sybase.public.sqlanywhere.general news group
    Last edited by pdreyer; 05-06-09 at 06:12. Reason: Updated link

  10. #10
    Join Date
    Oct 2007
    Posts
    29
    pdreyer:

    I actually got this simple counter to work now exept the case statement you wrote .. it had someting to do with how the syntax is typed i think. This one below works. But you do you put in youre case statement in this ?

    Code:
    declare @x int
    select @x=0
    update custom.t1
    set number=number+@x, @x=@x+1;
    // Magnus

  11. #11
    Join Date
    Oct 2007
    Posts
    29
    I had to change it to this to get it work, cant see why it isnt working within a case statement ?

    Code:
    UPDATE custom.t1
    set number=number+@i1, @i1=@i1+1
    where t1cat = 's1'
    
    UPDATE custom.t1    
    set number=number+@i2, @i2=@i2+1     
    where t1.cat = 's2'
    // Magnus

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •