Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2003
    Posts
    3

    Unanswered: Updating table records

    Two things.

    I will like to update the records in a column to be prefixed with two characters say, "OS", on a table with over 2 million records.

    Secondly, How do I remove these if need be.

    Thanks.

  2. #2
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Updating table records

    Add OS

    Update Table TwoMillionTable
    Set Col='OS'+Col


    Remove OS

    Update Table TwoMillionTable
    Set Col=Right(Col,Len(Col)-2)

  3. #3
    Join Date
    Dec 2003
    Posts
    3

    Updating table records

    Karolyn,

    I tried it and got the following error. Remember that I am using SQL Server.

    Server: Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'Table'.


    Thanks again.

  4. #4
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Updating table records

    sorrrry


    Update TwoMillionTable
    Set...

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Updating table records

    Originally posted by emelet
    Two things.

    I will like to update the records in a column to be prefixed with two characters say, "OS", on a table with over 2 million records.

    Secondly, How do I remove these if need be.

    Thanks.
    Remember that update of 2 million records is one transaction.... Server could hang... You have to split your update in couple smaller ones.

  6. #6
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Updating table records

    don't tell the Grand Poobah
    he will spank me again...

  7. #7
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Updating table records

    ...and remove your indexes before the update...
    reputing them after the update


    (i'm learning..., reading posts, and learning...)

  8. #8
    Join Date
    Sep 2003
    Posts
    522
    i've done this type of update on larger number of rows without using update. the trick? use queryout on bcp with values that you want to have, then truncate and bcp data back in.

  9. #9
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    interesting !!!
    you should post this answer to "Limteckboon"
    he's got an update perf problem


    ---> use Bulk Insert to put back in the data is faster than Bcp
    ---> and set the option 'select into/bulkcopy' to True

    exec sp_option 'DataBaseName', 'select into/bulkcopy','True'

  10. #10
    Join Date
    Sep 2003
    Posts
    522
    you're close, and i did

    bcp is just what i referred to as. bulk insert is faster

  11. #11
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    i've posted your idea to linkteckboon
    it might help him.

  12. #12
    Join Date
    Dec 2003
    Posts
    3

    Thumbs up

    Thanks ya'll.

Posting Permissions

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