UPDATE - Kinetica Docs

Updates can set columns to specified constant values or expressions.

UPDATE Syntax

UPDATE [<schema name>.]<table name>
SET
    <column 1> = <expression 1>,
    ...
    <column n> = <expression n>
[FROM <table expression>]
[WHERE <expression list>]

For example, to update employee with ID 5 to have a new manager, with ID 3, and to have a 10% salary increase:

UPDATE Example

UPDATE example.employee
SET
    sal = sal * 1.10,
    manager_id = 3
WHERE id = 5

Subqueries can be used in the expression list and within the SET clause. To update all the bottom earners in each department with a 5% salary increase using a subquery in the expression list:

UPDATE with Subquery in WHERE Clause Example

UPDATE example.employee b
SET sal = sal * 1.05
WHERE sal =
    (
        SELECT MIN(sal)
        FROM example.employee l
        WHERE b.dept_id = l.dept_id
    )

To update a backup table to match the source table that was just updated for new salary values using a subquery within the SET clause:

UPDATE with Subquery in SET Clause Example

UPDATE  example.employee_backup eb
SET     sal =
    (
        SELECT sal
        FROM example.employee e
        WHERE eb.id = e.id
    )

Joins can be used within an UPDATE statement to update records based on the results of a JOIN.

The JOIN statement cannot be part of the SET clause. Also, primary key column updates are not supported using JOIN operations. The table to be updated must be in the FROM clause.

For example, to update a backup table to match the source table that was just updated for new manager and salary values using an INNER JOIN:

UPDATE with JOIN Clause Example

UPDATE  eb
SET     sal = e.sal, manager_id = e.manager_id
FROM    example.employee_backup eb
JOIN    example.employee e ON eb.id = e.id

Simplified JOIN syntax is also supported:

UPDATE with JOIN Clause Alternate Example

UPDATE  eb
SET     sal = e.sal, manager_id = e.manager_id
FROM    example.employee_backup eb, example.employee e
WHERE   eb.id = e.id

Overwriting Duplicates

When updating the primary key value(s) of a record, it is possible for that updated record to collide with another record with the same primary key. To update the original record with the values specified in the SET clause and remove the other existing record with the same primary key, use the KI_HINT_UPDATE_ON_EXISTING_PK hint. If the target table has no primary key, this hint will be ignored.

This hint can be specified as the connection option UpdateOnExistingPk when using JDBC/ODBC.

UPDATE Overwrite Duplicates Example

UPDATE example.employee /* KI_HINT_UPDATE_ON_EXISTING_PK */
SET id = 1, sal = 123000
WHERE id = 6

By default, updates resulting in primary key collisions will be rejected, and the existing record(s) will remain unchanged. The KI_HINT_UPDATE_ON_EXISTING_PK hint overrides this behavior, favoring the original record being updated and the new values used to update it, specified in the SET clause, over the other existing record with the same primary key.

Ignoring Duplicates

When updating the primary key value(s) of a record, it is possible for that updated record to collide with another record with the same primary key. To discard updates that result in primary key collisions and not return an error, use the KI_HINT_IGNORE_EXISTING_PK hint. If the target table has no primary key or if using KI_HINT_UPDATE_ON_EXISTING_PK, this hint will be ignored.

These hints can be specified as connection options (IgnoreExistingPk, UpdateOnExistingPk) when using JDBC/ODBC.

UPDATE Ignore Duplicates Example

UPDATE example.employee /* KI_HINT_IGNORE_EXISTING_PK */
SET id = 1, sal = 123000
WHERE id = 6

​Overwriting Duplicates

​Ignoring Duplicates

Overwriting Duplicates

Ignoring Duplicates