Data Manipulation (DML) - Kinetica Docs

INSERT

There are two methods for inserting data into a table from within the database:

When specifying a column list using either method, any non-nullable fields not included in the list will be given default values—empty string for strings, and 0 for numerics. The fields in the column list and the values or fields selected must align.

If Multi-Head Ingest has been enabled on the database server, INSERT operations will automatically leverage it, when applicable.

See Loading Data for inserting data into a table from sources external to the database.

INSERT INTO … VALUES

Records with literal values can be inserted into the database.

INSERT INTO ... VALUES Syntax

INSERT INTO [<schema name>.]<table name> [(<column list>)]
VALUES (<column value list>)[,...]
[ON CONFLICT (<conflict column list>)
<
    DO NOTHING
    |
    DO UPDATE SET
    <
        <column 1> = <expression 1>,
        ...
        <column n> = <expression n>
        |
        (<column list>) = (<sub-select statement>)
    >
>
]
[WITH OPTIONS (<insert option name> = '<insert option value>'[,...])]

INSERT INTO ... VALUES Example

INSERT INTO example.employee (id, dept_id, manager_id, first_name, last_name, sal, hire_date)
VALUES
    (1, 1, null, 'Anne', 'Arbor', 200000, '2000-01-01'),
    (2, 2, 1, 'Brooklyn', 'Bridges', 100000, '2000-02-01'),
    (3, 3, 1, 'Cal', 'Cutta', 100000, '2000-03-01'),
    (4, 2, 2, 'Dover', 'Della', 150000, '2000-04-01'),
    (5, 2, 2, 'Elba', 'Eisle', 50000, '2000-05-01'),
    (6, 4, 1, 'Frank', 'Furt', 12345.67, '2000-06-01')

Insert Options

The following options can be specified to modify the way data is inserted (or not inserted) into the target table.

The WITH OPTIONS clause is only valid when used within JDBC.

BATCH SIZE

Use an ingest batch size of the given number of records.The default batch size is 50,000.

ON ERROR

When an error is encountered inserting a record, handle it using one of the following modes. The default mode is ABORT.

Mode	Description
`PERMISSIVE`	If an error is encountered parsing a source record, attempt to insert as many of the valid fields from the record as possible; insert a null for each errant value found.
`SKIP`	If an error is encountered parsing a source record, skip the record.
`ABORT`	If an error is encountered parsing a source record, stop the insert process. Primary key collisions are considered abortable errors in this mode.

ON CONFLICT

The ON CONFLICT clause controls the behavior when an inserted record conflicts with an existing record on the specified columns. Use DO NOTHING to silently discard the conflicting record, or DO UPDATE SET to update the existing record with new values.

The ON CONFLICT clause is only valid when used within an /execute/sql call.

Within the DO UPDATE SET clause, the special EXCLUDED reference provides access to the values of the record that was proposed for insertion.

INSERT INTO ... VALUES ... ON CONFLICT Example

INSERT INTO example.employee (id, dept_id, manager_id, first_name, last_name, sal, hire_date)
VALUES
    (6, 4, 1, 'Frank', 'Furt', 24680.50, '2000-06-01'),
    (7, 3, 3, 'George', 'Towne', 103050, '2000-03-02')
ON CONFLICT (id)
DO UPDATE SET
    last_name = EXCLUDED.last_name,
    dept_id = EXCLUDED.dept_id,
    manager_id = EXCLUDED.manager_id,
    sal = EXCLUDED.sal

INSERT INTO … SELECT

Records that are the result set of a query can be inserted into the database.

INSERT INTO ... SELECT Syntax

INSERT INTO [<schema name>.]<table name> [(<column list>)]
<select statement>
[ON CONFLICT (<conflict column list>)
<
    DO NOTHING
    |
    DO UPDATE SET
    <
        <column 1> = <expression 1>,
        ...
        <column n> = <expression n>
        |
        (<column list>) = (<sub-select statement>)
    >
>
]

INSERT INTO ... SELECT Example

INSERT INTO example.employee_backup (id, dept_id, manager_id, first_name, last_name, sal)
SELECT id, dept_id, manager_id, first_name, last_name, sal
FROM example.employee
WHERE hire_date >= '2000-04-01'

The ON CONFLICT clause can also be used with INSERT INTO ... SELECT. See ON CONFLICT for details.

ON CONFLICT

The ON CONFLICT clause is only valid when used within an /execute/sql call.

Within the DO UPDATE SET clause, the special EXCLUDED reference provides access to the values of the record that was proposed for insertion.

INSERT INTO ... SELECT ... ON CONFLICT Example

INSERT INTO example.employee_backup (id, dept_id, manager_id, first_name, last_name, sal, hire_date)
SELECT id, dept_id, manager_id, first_name, last_name, sal, hire_date
FROM example.employee
WHERE hire_date >= '2000-04-01'
ON CONFLICT (id)
DO UPDATE SET
    last_name = EXCLUDED.last_name,
    dept_id = EXCLUDED.dept_id,
    manager_id = EXCLUDED.manager_id,
    sal = EXCLUDED.sal

Upserting

To upsert records, inserting new records and updating existing ones (as denoted by 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.

Upsert Example

INSERT INTO example.employee_backup /* KI_HINT_UPDATE_ON_EXISTING_PK */
SELECT *
FROM example.employee
WHERE hire_date >= '2000-01-01'

By default, any record being inserted that matches the primary key of an existing record in the target table will be discarded, and the existing record will remain unchanged. The KI_HINT_UPDATE_ON_EXISTING_PK hint overrides this behavior, favoring the source records over the target ones.

Ignoring Duplicates

To discard duplicate records (as denoted by primary key) when inserting data, use the KI_HINT_IGNORE_EXISTING_PK hint. If the target table has no primary key or if in upsert mode (using KI_HINT_UPDATE_ON_EXISTING_PK), this hint will be ignored.

Both of these hints can be specified as the connection options IgnoreExistingPk & UpdateOnExistingPk, respectively, when using JDBC/ODBC.

Ignore Duplicates Example

INSERT INTO example.employee_backup /* KI_HINT_IGNORE_EXISTING_PK */
SELECT *
FROM example.employee
WHERE hire_date >= '2000-01-01'

UPDATE

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

MERGE INTO

Merges records from a source table or query into a target table. When a record in the source matches one in the target (based on the ON condition), the target record can be updated; when there is no match, a new record can be inserted. At least one of the WHEN MATCHED or WHEN NOT MATCHED clauses must be specified.

MERGE INTO Syntax

MERGE INTO [<target schema name>.]<target table name> [[AS] <alias>]
USING
(
    [<source schema name>.]<source table name>
    | <source select statement>
)
ON <expression list>
[WHEN MATCHED THEN UPDATE SET
    <column 1> = <expression 1>,
    ...
    <column n> = <expression n>
]
[WHEN NOT MATCHED THEN INSERT [(<column list>)] VALUES (<column value list>)]

The USING clause specifies the source of the data to merge, which can be either a table or a SELECT statement. The ON clause specifies the condition(s) used to determine whether a source record matches a target record. The WHEN MATCHED clause specifies the UPDATE to apply to matching target records. The WHEN NOT MATCHED clause specifies the INSERT to apply for source records that have no match in the target table. For example, to merge a source table into a target table, updating existing records and inserting new ones:

MERGE INTO Example

MERGE INTO target t
USING source s
ON t.id = s.id
WHEN MATCHED THEN
    UPDATE SET
        name = s.name,
        quantity = s.quantity,
        updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (id, name, quantity, updated_at)
    VALUES (s.id, s.name, s.quantity, CURRENT_TIMESTAMP)

DELETE

Deletes records from a table.

DELETE Syntax

DELETE
FROM [<schema name>.]<table name>
[WHERE <expression list>]

For example, to delete employee with ID 6:

DELETE Example

DELETE
FROM example.employee
WHERE id = 6

Subqueries can also be used in the expression list. To delete all the most recent hires in each department:

DELETE with Subquery Example

DELETE
FROM example.employee b
WHERE hire_date =
    (
        SELECT MAX(hire_date)
        FROM example.employee l
        WHERE b.dept_id = l.dept_id
    )

​INSERT

​INSERT INTO … VALUES

​Insert Options

​ON CONFLICT

​INSERT INTO … SELECT

​ON CONFLICT

​Upserting

​Ignoring Duplicates

​UPDATE

​Overwriting Duplicates

​Ignoring Duplicates

​MERGE INTO

​DELETE

INSERT

INSERT INTO … VALUES

Insert Options

ON CONFLICT

INSERT INTO … SELECT

ON CONFLICT

Upserting

Ignoring Duplicates

UPDATE

Overwriting Duplicates

Ignoring Duplicates

MERGE INTO

DELETE