Re: Nested SELECT in UPDATE statement

[Lukas Eder]

Hi Ben,

> I was wondering whether there is an example of how to do a nested select in
> an update statement:
>
> UPDATE table SET column = (SELECT something FROM somewhere)

I'm afraid there isn't an example in the manual. I'll fix this soon:
https://sourceforge.net/apps/trac/jooq/ticket/1558

> This appears to be the subject of this ticket
> (http://sourceforge.net/apps/trac/jooq/ticket/60), which is marked as being
> fixed, so I assume that you must be able to build the above construct using
> the DSL.

Right. Note that org.jooq.Select implements org.jooq.FieldLike, which
exposes an asField() method:
http://www.jooq.org/javadoc/latest/org/jooq/FieldLike.html

Essentially, you can achieve this as such:
create.update(TABLE)
.set(COLUMN, create.select(something).from(somewhere).asField())

Hope this helps. Probably it would make sense to add .set(Field<?>,
Select<?>) methods to various DSL API types, for convenience. I'll
track this as #1559:
https://sourceforge.net/apps/trac/jooq/ticket/1559

Cheers

[Lukas Eder]

> Essentially, you can achieve this as such:
> create.update(TABLE)
> .set(COLUMN, create.select(something).from(somewhere).asField())

Also, I guess you'll have to employ an unsafe type-cast. This is a bit
annoying. I'll see what can be done to improve this part of the jOOQ
API

[Ben Hood]

Hi Lukas,

On Thursday, July 12, 2012 1:02:29 PM UTC+1, Lukas Eder wrote:

Essentially, you can achieve this as such:
create.update(TABLE)
  .set(COLUMN, create.select(something).from(somewhere).asField())

Thanks for such a quick response to my question :-)

I hadn't seen the asField() operator, so I'll give that a go now.

Cheers,

Ben 

[Ben Hood]

On Thursday, July 12, 2012 1:12:20 PM UTC+1, Ben Hood wrote:

On Thursday, July 12, 2012 1:02:29 PM UTC+1, Lukas Eder wrote:

Essentially, you can achieve this as such:
create.update(TABLE)
  .set(COLUMN, create.select(something).from(somewhere).asField()) 

One thing I forgot to ask was what it would look like when you want to update more than 1 column with the same select statement, e.g.

UPDATE Table SET col1, col2

SELECT something, something_else FROM somewhere

?

[Lukas Eder]

> One thing I forgot to ask was what it would look like when you want to
> update more than 1 column with the same select statement, e.g.
>
> UPDATE Table SET col1, col2
> SELECT something, something_else FROM somewhere

That will depend on the database that you're using. In standard SQL
UPDATE statements, this isn't possible (without writing several SELECT
statements)

If you're using any of those databases that support the standard SQL
MERGE statement, then you could use a SQL statement as such

MERGE INTO Table dst
USING (

SELECT something, something_else
FROM somewhere

) src
ON (1 = 1) -- replace this by a more sensible join condition
WHEN MATCHED THEN UPDATE SET
dst.col1 = src.something,
dst.col2 = src.something_else

MERGE statements are supported by jOOQ. Writing them may turn out to
be a bit clumsy because of the aliasing that is ususally required...
H2's MERGE statement is now also supported, as of jOOQ 2.4.0

If you're using MySQL, I guess you could trick the INSERT INTO ...
SELECT ... ON DUPLICATE KEY UPDATE statement into doing what you want:
http://dev.mysql.com/doc/refman/5.6/en/insert.html

Postgres natively supports UPDATE ... FROM, but this is not (yet)
supported by jOOQ:
http://www.postgresql.org/docs/9.2/static/sql-update.html

[Ben Hood]

On Thursday, July 12, 2012 1:37:49 PM UTC+1, Lukas Eder wrote:

That will depend on the database that you're using. In standard SQL
UPDATE statements, this isn't possible (without writing several SELECT
statements)

The code base we're using targets Oracle, MySQL and HSQL. We have successfully used the onDuplicateKeyUpdate() portably across all 3 databases, which was a strong reason to use JOOQ :-)

So if I understand you correctly, in this particular scenario, there is no portable compile-time JOOQ construct we could use - rather we would have to make the application code dialect aware and construct DB-specific JOOQ objects for each of the databases we support? 

[Lukas Eder]

> The code base we're using targets Oracle, MySQL and HSQL. We have
> successfully used the onDuplicateKeyUpdate() portably across all 3
> databases, which was a strong reason to use JOOQ :-)

Great, good to know!

> So if I understand you correctly, in this particular scenario, there is no
> portable compile-time JOOQ construct we could use - rather we would have to
> make the application code dialect aware and construct DB-specific JOOQ
> objects for each of the databases we support?

In this particular case, you can't use jOOQ to translate MERGE or ON
DUPLICATE KEY UPDATE syntax to all of your three databases (yet).