I’ve been using sql for a number of years now, and thought i knew most of the sql syntax. So i was quite surprised to find out about the existence of UPDATE … FROM. Turns out, this doesn’t exist in Oracle, but is supported by PostgreSQL.
Here’s a small example:
update response
set house_id = ?
from house as h
where response_text = 'Offered'
and house_id = h.id
and h.address = ?
In this example the response table is joined with the house table to find the responses that need to be updated. You could probably achieve this in oracle with an inline view in the where clause, but this syntax is pretty readable.
I also wanted to try what this would look like in Grails with the hibernate criteria api. I think the following should achieve the same, but i got some weird exception, so there must be something wrong.
def results = Response.withCriteria{
house {
eq("address",myHouse.address)
}
eq("responseText","Offered")
}
results.each {
it.house = myHouse
it.save(flush:true)
}
I think the criteria api could be more readable than sql in some cases, but from a performance point of view it doesn’t make sense in this case. Why query all data, move the data to the app server, change the data, move it back to the database, and execute the update statements, when you can do it all in one statement?
Anyway, if anybody knows why my criteria is failing, please enlighten me. (it’s throwing an exception on the house { eq… } parts, something about House.call() not existing…