efficient way to update data

[shikhar singh]

Hi
I want to achieve a simple functionality but in a very efficient way. let me ask using an example:
I want to update the department of first 20 employees where employee manager is 'Ramesh'(say). And there are 500 employees under Ramesh and 5000 employees in the company.
And employees table have following fields:
Employee ID(PK), Manager name, department.
How can I meet this goal without firing minimum number of queries.?
I don't want to use batch statements.
Actually making the batch of different SQL statements and submitting it to database will avoid the number of calls made to the database. But the number of queries will be the same.
Your view will be very helpful
Thanks

[Angela Lamb]

Why can't you just use an update statement?
update tableName set department=? where managerName=?

[singh udit]

Hi,
I don't want to change the department of all the employees under 'Ramesh'.
I want to make this updation for just first 20 employees(out of 5000). By first i mean, first 20 employees sorted by emploee ID.
Thanks

[Thomas Paul]

use an updatable ResultSet and update the first 20 rows.

[singh udit]

Hi,
Thanks for reply..
By following the above approach, I've to do the following things:
1. Select all rows from database in a resultset, corresponing to the specific manager.(it can be a huge number).
2. Update each row.(first 20 rows)
Actually I want to avoid step 1, because this may involve unnecessary selection of data in the resultset.(say 5000 records are selected, out of which I want to update just first 20).
I can avoid the overhead of step 2 by sending all the queries in a batch.
Any views?
Thanks

[Naresh Babu]

U can do it using rownum like:
update tablename set columnname=XXX where manager='Ramesh'
and rownum<21
Thanks!
[This message has been edited by Naresh Babu (edited March 23, 2001).]

[Thomas Paul]

Remember hat a ResultSet doesn't actually pull the data from the database in one huge block. It represents a cursor into the dataset. So even though you may get 5,000 hits you aren't actually bringing over 5,000 rows.
rownum must be database specific. It doesn't work with either Sybase or Oracle.

[Naresh Babu]

I know for sure that rownum works with Oracle sine i use it
in Oracle.

Originally posted by Thomas Paul:
Remember hat a ResultSet doesn't actually pull the data from the database in one huge block. It represents a cursor into the dataset. So even though you may get 5,000 hits you aren't actually bringing over 5,000 rows.
rownum must be database specific. It doesn't work with either Sybase or Oracle.

[Brian Nice]

I thought he was wanting them back in a specific order too? If I recall, and I may be wrong on this, but if you use rownum with an order by, it orders the rows returned, and those rows returned are not necessarily the first 20 rows in order.
Brian

[singh udit]

I am totalLy ageed with Brian.
I want to update rows in a particular order in DB.
Like change to departmentID of first 20 employees under Manger 'XYZ', sorted by 'date of joining'.
Using rownum will update the first 20 rows meeting the 'where clause' criteria.