Update on large number of rows

Cathy123 · New User Joined: 07 Jul 2006 Posts: 4 Location: Chennai

I have a table Emp with the data's as below

Emp-id Emp-code
1111 A
2222 A
3333 B
4444 C
5555 B

There are 10,000 such records in this table .

Given as input to a Program is the Emp-id of 200 employees

What I need to do is to update the Emp-code of the 200 employees to code "A"

At present my idea is to put the UPDATE query in a LOOP that runs for 200 times.

UPDATE EMP
SET EMP_CODE = 'A'
WHERE EMP_ID = :EMP-ID

But this would naturally consume more CPU TIME. Mine is an Online module. Is there any way to minimize the CPU time?

Also since I'm going to hold the table for a longer time I may get -911 error. Any solution to reduce this error?

Please Help me out!!!!

William Thompson · Posted: Tue Feb 20, 2007 4:40 pm

Why are you doing batch work in an online module?
Have you considered using commits?

Cathy123 · New User Joined: 07 Jul 2006 Posts: 4 Location: Chennai

William,

Thanks for ur immediate reply

This is not a Batch work. I've just provided EMP table as an example

In reality We have a Online trading screen ( Trading of securities)

In that screen, the user lists down the trades and sends that to the Back End for Processing

What we receive at the BACK END is the TRADE-ID ( Primary key)

So if the user lists down 200 trades, we get 200 TRADE-ID's
For these trades we need to update the TRADE-INDICATOR TO 'Y' in the table

Our TABLE

TRADE-ID TRADE-INDICATOR
1111 N
2222 N
3333 N
4444 N

Further, If i choose to do UPDATE in a loop 200 times, I will need to use a commit.