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

Query help

 
vijaya bacina
Ranch Hand
Posts: 155
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 536
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
UPDATE table1
SET column = table2.column
FROM table2
WHERE table1.column > table2.column
 
vijaya bacina
Ranch Hand
Posts: 155
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 155
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic