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:

In SQL:

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