Hello Sascha, > is there a way to join tables in an update query? Had a quick look at the > interfaces and I can't find something that would help me here. Is it even > feasible for a library like this? I know it works in MySQL and Sql Server, > but I don't know if it works with other database servers.
These vendor-specific UPDATE extensions are currently not supported by jOOQ. There is a pending feature request for the Postgres UPDATE .. FROM clause, which is similar to what you need. It's nice to know that MySQL and SQL Server both have a similar syntax: https://sourceforge.net/apps/trac/jooq/ticket/1018 There's another ticket regarding UPDATE .. LIMIT .. OFFSET constructs here: https://sourceforge.net/apps/trac/jooq/ticket/714 Or UPDATE .. RETURNING: https://sourceforge.net/apps/trac/jooq/ticket/834 Anyway, you can usually circumvent the UPDATE .. FROM .. JOIN syntax by using nested selects in UPDATE's: UPDATE table1 SET column = ( SELECT .. FROM table2 WHERE table2.id = table1.id ) This is supported by jOOQ. > PS: Since this is my first post here I want to say that this is an awesome > project! Thank you! > Right now I'm refactoring the persistence layer of our product > where we had lots of code that creates SQL on the fly for either MySQL or > SQL Server. Most of the old stuff worked for both but there were always > small differences which made the code a mess. Yes, those differences can be very subtle and turn out to require a lot of patching in client code, both for making SQL and JDBC usage compatible. There are many examples for that on my blog, too: http://lukaseder.wordpress.com/2011/08/29/postgres-insert-returning-clause-and-how-this-can-be-simulated-in-other-rdbms/ http://lukaseder.wordpress.com/2011/08/31/rdbms-bind-variable-casting-madness/ http://lukaseder.wordpress.com/2012/02/19/subtle-sql-differences-identity-columns/ etc... Cheers Lukas
