File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Query help Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Query help" Watch "Query help" New topic
Author

Query help

vijaya bacina
Ranch Hand

Joined: Aug 23, 2005
Posts: 155
Hi
i had a query like this
update table1 set table1.column=(select column from table2) where table1.colum2>(select column from table2)
can anybody tell me how to avoid "select column from table2" repeating?
Richard Green
Ranch Hand

Joined: Aug 25, 2005
Posts: 536
UPDATE table1
SET column = table2.column
FROM table2
WHERE table1.column > table2.column


MCSD, SCJP, SCWCD, SCBCD, SCJD (in progress - URLybird 1.2.1)
vijaya bacina
Ranch Hand

Joined: Aug 23, 2005
Posts: 155
actually the query is like this
update table1 set table1.column=(select min(column) from table2) where table1.colum2>(select min(column) from table2)
i tried ur way but i got error saying
UPDATE table1
SET column = min(table2.column)
FROM table2
WHERE table1.column > min(table2.column)

ORA-00934: group function is not allowed here
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
You have to use 2 seperate queries; if you do it in a stored procedure, then it will be one round trip to the database. However, unless this thing gets execute a lot, I'd just code it as 2 seperate queries, the extra time for the extra round trip to the DB should be about as minimimal as a DB call can get.
vijaya bacina
Ranch Hand

Joined: Aug 23, 2005
Posts: 155
Hi,
I wanted to avoid repeation of "select min(column) from table2)" because table2 has some million records. then it would be good to find min(coulmn) once and use it in condition..is there any way to do it.? or is it not possible?
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Originally posted by vijaya bacina:
Hi,
I wanted to avoid repeation of "select min(column) from table2)" because table2 has some million records. then it would be good to find min(coulmn) once and use it in condition..is there any way to do it.? or is it not possible?


If you have an index on the column of table 2, then it doesn't take too much time (less than 6/10ths of a second for the entire query on my database, when table 2 has 1,101,239 rows and table 1 has 3 rows), even when the min is found twice (most of the time goes to getting the consistent blocks into memory, which only happens once, unless your database is really really grossly overloaded).

If that's still not acceptable, use 2 statements:

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Query help