How to duplicate and modify table rows using Jooq insertInto How to duplicate and modify table rows using Jooq insertInto sql sql

How to duplicate and modify table rows using Jooq insertInto

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:


INSERT INTO registration (col1, col2, col3, stage, col4, col5)SELECT col1, col2, col3, 6, col4, col5FROM registrationWHERE 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();}

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();    }