• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Query help

 
Ranch Hand
Posts: 155
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Ranch Hand
Posts: 536
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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:

 
When I was younger I felt like a man trapped inside a woman’s body. Then I was born. My twin is a tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic