Results 1 to 9 of 9
  1. #1
    Join Date
    May 2012
    Posts
    2

    Unanswered: sybase-updating all rows in a table

    hi all

    need your help please. i am trying to update all the million rows in a table. my structure is
    talbe a (id int,
    city char(10),
    country char(10)
    )
    id is the unique clustered index.
    My aim is to update the fields city and country with id all across the table.
    i am getting stuck trying to use set rowcount 1000 becuase its going in a endless loop. Please help me with this code. Appreciate it.

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    What are the conditions for an update of a row? Can you give an example of the current rowvalues and the new values?
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    May 2012
    Posts
    2

    sybase-updating all rows in a table

    Hi,

    The data is as follows:

    before:

    id city country
    1 Delhi India
    78 New York New York

    After:

    Id City Country
    1 1 1
    78 78 78

    There is no condition actually. I have to update all the rows in the table.
    Thanks.

  4. #4
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hello,
    If the values are to equal in all the three columns, just drop the last two, and only use id. When you need the 3 values select as alias. You save space on the db and the effort of the update.

    Hope it helps.

  5. #5
    Join Date
    May 2012
    Posts
    6
    New to sybase, but the SQL for this is surely just

    UPDATE table
    SET city = id, country = id

    ?!?!

    Am I missing something?

  6. #6
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by SharkEnergy View Post
    New to sybase, but the SQL for this is surely just

    UPDATE table
    SET city = id, country = id

    ?!?!

    Am I missing something?
    I think the number of records to be processed might be the bottleneck here. It might fill up the transactionlog too fast.
    I'm not crazy, I'm an aeroplane!

  7. #7
    Join Date
    May 2012
    Posts
    6
    True. Wondering why original poster is having issues with rowcount.

    How about...

    WHILE (SELECT count(id) from TABLENAME WHERE NOT ( id = city)) > 0
    BEGIN
    SET ROWCOUNT1000

    UPDATE TABLENAME
    SET city = id, country = id where not id = city
    END


    May need to stick a COMMIT or checkpoint in there to force the set? Maybe.
    Last edited by SharkEnergy; 05-07-12 at 09:54.

  8. #8
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    He mentioned an endless loop. I've never dealt with this kind of situations before, so I'm not sure how/if this works. I'll keep searching.
    I'm not crazy, I'm an aeroplane!

  9. #9
    Join Date
    May 2012
    Posts
    6
    Think the endless loop using rowcount can only be because he hasn't prompted it to move to the non updated set of data after the first update of 1000 rows, therefore it continues to update the same 1000 rows. The where clause I've used above should solve that! Hope it helps

Posting Permissions

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