Updating more than one rows at a time

  • First:

    Define a function that maps employeeID to col1's value.. If you can create such a function all you have to do is:

    update table1

    set col1=select convertEmployeeIdToCol1(id)

    Secondly: Updating data for 200,000 rows is inviting trouble. Instead, go for limited no of updates at a time. like update few 10,50,100...number of rows at time....

    You can also thread the operation with .NET

    Hope this helps

  • Hi Guys,

    Instead of doing it with query I did it through the coding

    Thanks for your help.

    It's really valuable help for me thank again 🙂

    Thank,

    Rohit.

  • First:

    Define a function that maps employeeID to col1's value.. If you can create such a function all you have to do is:

    update table1

    set col1=select convertEmployeeIdToCol1(id)

    My concern about this from my own experience, is that functions, while they can make a problem appear much simpler, can result in some major performance problems. If the function contains a single SELECT from some table, you have just converted a single implied SELECT (where we are getting our data from for the UPDATE) into 200,000 individual SELECTS.

    Scott

  • Perry Whittle (9/8/2008)


    where exists

    would be more efficient than

    where in

    Are you sure about that one? Like I tested it and in this case exists is faster than in?

  • Ninja's_RGR'us (9/15/2008)


    Like I tested it and in this case exists is faster than in?

    thats what i'm asking

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply