Skip to main content

Target Constraint - Do Update

Definition​

This action indicates that an update should be performed when a conflict occurs on a specific constraint.

Available methods​

1. targetConstraint(String constraint)​

  • constraint: the name of constraint where the conflict must occur.

2. doUpdate()​

  • It does not receive parameters.

3. set(KTableColumn kTableColumn, KColumnAllowedToSetUpdate kColumnAllowedToSetUpdate)​

4. set(KTableColumn kTableColumn, KQuery kQuery)​

  • kTableColumn: is the column that will be updated.
  • kQuery: is a subquery whose result will be assigned to the column.

5. set(KTableColumn kTableColumn, Object object)​

  • kTableColumn: is the column that will be updated.
  • object: is the value that will be assigned to the column.
    Among the possible values are: Number, String, LocalDate, LocalDateTime, UUID.
info

To make use of this action, you must call the targetConstraint, doUpdate and set methods (in this order) right after the onConflict method.

tip

The set method can be called as many times as columns you need to update.

How to reference the current and the excluded record in a set method?​

When you are resolving a conflict, if you need to pass a column as the new value in the set clause, the current record column can be referenced throug the useTableNameAsAlias method and the excluded record column throug the excluded method.

Calling useTableNameAsAlias and excluded from a KTableColumn​

1. excluded()​

It does not receive any parameters.

2. useTableNameAsAlias()​

It does not receive any parameters.

info

In both methods, the KTableColumn that invokes the method will be the affected.

Example​

Java code:

final KValues valuesInsert = values()
    .append("English")
    .append("Spanish");

k
.insertInto(LANGUAGE)
.columns(LANGUAGE.NAME)
.values(valuesInsert)
.onConflict()
.targetConstraint("pk_language")
.doUpdate()
.set(
    LANGUAGE.NAME, 
    concat(
        LANGUAGE.NAME.excluded(),
        val(" "),
        LANGUAGE.NAME.useTableNameAsAlias()
    )
)
.execute();

SQL generated:

INSERT INTO language (name)
VALUES (?1), (?2)
ON CONFLICT ON CONSTRAINT "pk_language"
DO UPDATE
SET name = CONCAT(EXCLUDED.name || ?3 || language.name)

Parameters:

  • ?1: "English"
  • ?2: "Spanish"
  • ?3: " "

Calling useTableNameAsAlias and excluded from the KFunction class​

1. excluded(KTableColumn kTableColumn)​

  • kTableColumn: is the column that will be the affected.

2. useTableNameAsAlias(KTableColumn kTableColumn)​

  • kTableColumn: is the column that will be the affected.

To use this way, you need to import the static functions as follows:

import static com.myzlab.k.KFunction.*;

Example​

Java code:

final KValues valuesInsert = values()
    .append("English")
    .append("Spanish");

k
.insertInto(LANGUAGE)
.columns(LANGUAGE.NAME)
.values(valuesInsert)
.onConflict()
.targetConstraint("pk_language")
.doUpdate()
.set(
    LANGUAGE.NAME, 
    concat(
        excluded(LANGUAGE.NAME),
        val(" "),
        useTableNameAsAlias(LANGUAGE.NAME)
    )
)
.execute();

SQL generated:

INSERT INTO language (name)
VALUES (?1), (?2)
ON CONFLICT ON CONSTRAINT "pk_language"
DO UPDATE
SET name = CONCAT(EXCLUDED.name || ?3 || language.name)

Parameters:

  • ?1: "English"
  • ?2: "Spanish"
  • ?3: " "