Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

efficient way to update data

 
shikhar singh
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 156
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why can't you just use an update statement?
update tableName set department=? where managerName=?
 
singh udit
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
mister krabs
Ranch Hand
Posts: 13974
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
use an updatable ResultSet and update the first 20 rows.
 
singh udit
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
mister krabs
Ranch Hand
Posts: 13974
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 195
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic