Updating more than one rows at a time

Question

Post reply

Updating more than one rows at a time

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

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

Monica Chaturvedi SSC Journeyman Points: 80 More actions · Answer 1

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

Rohit-760015 Valued Member Points: 64 More actions · Answer 2

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.

Scott D-810830 SSC-Addicted Points: 494 More actions · Answer 3

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

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?

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

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" 😉