Meaningless Drivel is fun!*
The moose likes JDBC and the fly likes efficient way to update data Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "efficient way to update data" Watch "efficient way to update data" New topic
Author

efficient way to update data

shikhar singh
Ranch Hand

Joined: Oct 02, 2000
Posts: 31
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

Joined: Feb 22, 2001
Posts: 156
Why can't you just use an update statement?
update tableName set department=? where managerName=?
singh udit
Greenhorn

Joined: Mar 01, 2001
Posts: 7
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

Joined: May 05, 2000
Posts: 13974
use an updatable ResultSet and update the first 20 rows.


Associate Instructor - Hofstra University
Amazon Top 750 reviewer - Blog - Unresolved References - Book Review Blog
singh udit
Greenhorn

Joined: Mar 01, 2001
Posts: 7
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

Joined: Mar 20, 2001
Posts: 29
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

Joined: May 05, 2000
Posts: 13974
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

Joined: Mar 20, 2001
Posts: 29
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

Joined: Nov 02, 2000
Posts: 195
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

Joined: Mar 01, 2001
Posts: 7
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: efficient way to update data
 
Similar Threads
Simple requirement .. but I don't know how!
Simple requirement .. but I don't know how!
business model to view model anamoly
Doubt in Entity Relationship Persistence
Question for SCBCD aspirants (and gurus)...