Hello Lukas,
thanks for the quick reply.
Basically I only need to set the columns to constant values where the
joined table has a specifiec field value. The updated table and the joined
table are the same. However your response inspired me to create the
following query:
EventTable eventTable = EventTable.alias(DOMAIN, 0);
EventTable contextEventTable = EventTable.alias(DOMAIN, 1);
getFactory()
.update(eventTable)
.set(eventTable.DISPLAY_TEXT, (String) null)
.set(eventTable.DISPLAY_TEXT_VALID, (byte) 0)
.set(eventTable.DISPLAY_INFO, (String) null)
.set(eventTable.DISPLAY_INFO_VALID, (byte) 0)
.where(
Factory.exists(
getFactory()
.select(contextEventTable.ID)
.from(contextEventTable)
.where(eventTable.CONTEXT_EVENT_ID.equal(contextEventTable.ID))
.and(contextEventTable.EVENT_DEFINITION_ID.equal(eventDefinition.getId()))
)
)
.execute();
It works for SQL Server, but MySQL doesn't like it. Seems MySQL does not
allow you to do a sub-query on the same table that you are trying to
update. Workarounds are to use a join (solution I had before with manual
SQL) or do a nested sub-query, which would create a temporary table
(http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause).
The second workaround would work with jooq, but has bad performance - which
is critical in this case.
Guess I'll stick with manual SQL for now. Thanks again,
Sascha
Am Freitag, 2. März 2012 11:43:39 UTC+1 schrieb Lukas Eder:
>
> 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
>
>
