How to duplicate and modify table rows using Jooq insertInto

Question

I'm using Jooq and am trying to generate a near copy of a data set within the same table. In the process I want to update the value of one field to a known value. I've been looking at the docs & trying variations with no luck yet. Here is my approach updating the REGISTRATION table and setting the 'stage' field to the value 6 (where it was 5). So I'll end up with the original data plus a duplicate set with just the different stage value. in pseudo code

insert into Registration (select * from Registration where stage=5) set stage=6 

I tried this code below and thinking I could add a ".set(...)" method to set the value but that doesn't seem to be valid.

create.insertInto(REGISTRATION)
    .select(
        (selectFrom(REGISTRATION)
            .where(REGISTRATION.STAGE.eq(5))
        )
    ).execute();

Brunt

Interesting syntax. What database supports an additional SET clause after an INSERT .. SELECT? – Hadlock

Answer 1

I'm not aware of a database that supports an INSERT .. SELECT .. SET syntax, and if there were such a syntax, it certainly isn't SQL standards compliant. The way forward here would be to write:

In SQL:

INSERT INTO registration (col1, col2, col3, stage, col4, col5)
SELECT col1, col2, col3, 6, col4, col5
FROM registration
WHERE stage = 5;

In jOOQ:

create.insertInto(REGISTRATION)
      .columns(
         REGISTRATION.COL1,
         REGISTRATION.COL2,
         REGISTRATION.COL3,
         REGISTRATION.STAGE,
         REGISTRATION.COL4,
         REGISTRATION.COL5)
      .select(
         select(
           REGISTRATION.COL1,
           REGISTRATION.COL2,
           REGISTRATION.COL3,
           val(6),
           REGISTRATION.COL4,
           REGISTRATION.COL5)
        .from(REGISTRATION)
        .where(REGISTRATION.STAGE.eq(5)))
      .execute();

The following static import is implied:

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

In jOOQ, dynamically

Since you're looking for a dynamic SQL solution, here's how this could be done:

static <T> int copy(
    DSLContext create, Table<?> table, Field<T> field, 
    T oldValue, T newValue
) {
    List<Field<?>> into = new ArrayList<>();
    List<Field<?>> from = new ArrayList<>();

    into.addAll(Stream.of(table.fields())
                      .filter(f -> !field.equals(f))
                      .collect(toList()));
    from.addAll(into);

    into.add(field);
    from.add(val(newValue));

    return
    create.insertInto(table)
          .columns(into)
          .select(
             select(from)
            .from(table)
            .where(field.eq(oldValue))
          .execute();
}

Hadlock

Thanks but I was trying to avoid this explicit column by column approach as I want to apply this across multiple tables. The only common column across all of them will be this 'stage' column that will be set to the same value. I wonder if there's a Field list oriented approach? – Brunt

This is the field list oriented approach. You can pass arrays or collections to all of these methods and assemble them dynamically. Always remember, every jOOQ query is a dynamic SQL query: jooq.org/doc/latest/manual/sql-building/dynamic-sql – Hadlock

Interesting link, didn't realise jOOQ could do this. I get the concept that I should pass the select statements the list of fields from each table. And the innermost select should be passed the same list but with the 'stage' value substituted. I think getting the syntax right might be a bit of a challenge - more reading required! – Brunt

@wholeroll: No problem at all. I've updated my answer with a dynamic version to illustrate your case... – Hadlock

Answer 2

Thanks Lukas for your answer which I'll use a version of as it's nice and general. My own answer which I just got to work is less general but might be a useful reference for other people who come this way especially as it takes account of the identity field "id" which can otherwise cause problems.

public void duplicate(int baseStage, int newStage) {

        Field<?>[] allFieldsExceptId = Stream.of(REGISTRATION.fields())
                                            .filter(field -> !field.getName().equals("id"))
                                            .toArray(Field[]::new);

        Field<?>[] newFields = Stream.of(allFieldsExceptId).map(field -> {
            if (field.getName().contentEquals("stage")) {
                return val(newStage);
            } else {
                return field;
            }
        }).toArray(Field[]::new);


        create.insertInto(REGISTRATION)
                .columns(allFieldsExceptId)
                .select(
                        select(newFields)
                            .from(REGISTRATION)
                            .where(REGISTRATION.STAGE.eq(baseStage)))
                .execute();
    }

Brunt

Thanks for documenting this. And congrats to jOOQ question #1000 on Stack Overflow! :) – Hadlock