CODEWITHSUNDEEP
javamysqlsqljooq
rkudva
rkudva

Reputation: 177

INSERT .. SELECT with some default values in MySQL with JOOQ

Lets say I have a table Person(id, fname, lname) and it contains a record (1, 'Michael', 'Bay'). Now I wish to create another record in Person table with the same fname and lname but different id i.e. (453456, 'Michael', 'Bay'). This is the way I would do in plain SQL

INSERT INTO Person(id, fname, lname)
SELECT 453456, Person.fname, Person.lname
FROM Person
WHERE Person.id = 1;

How can this be done with JOOQ (ideally while retaining JOOQ's code generation and type safety features)?

I am aware that JOOQ provides ability to copy entire records from one table to same or another table with its selectFrom syntax

jooq.insertInto(PERSON)
      .select(selectFrom(PERSON).where(PERSON.ID.eq(1)))
      .execute();

But in my case, there are only certain columns in the record that I need to copy while the rest of the values needed to be set explicitly

Another solution I could think of is the following

jooq.insertInto(PERSON)
      .values(452452)
      .execute();

jooq.update(PERSON)
  .set(row(PERSON.FNAME, PERSON.LNAME),
       select(PERSON.FNAME, PERSON.LNAME)
      .from(PERSON)
      .where(PERSON.ID.eq(1)))
  .where(PERSON.ID.eq(452452))
  .execute();

But it doesn't feel right. I would be grateful if someone could provide any other solution/workaround for this problem.

Upvotes: 4

Views: 2351

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220842

jooq.insertInto(PERSON)
    .columns(PERSON.ID, PERSON.FNAME, PERSON.LNAME)
    .select(select(val(452452), PERSON.FNAME, PERSON.LNAME)
           .from(PERSON)
           .where(PERSON.ID.eq(1)))
    .execute();

As always, this is assuming the following static import:

import static org.jooq.impl.DSL.*;

Upvotes: 4

Related Questions