Data Manipulation (DML)

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.

Tip

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

See Loading Data from Files 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
1
2
INSERT INTO [<schema name>.]<table name> [(<column list>)]
VALUES (<column value list>)[,...]
INSERT INTO...VALUES Example
1
2
3
4
5
6
7
8
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.6789, '2000-06-01')

INSERT INTO...SELECT

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

INSERT INTO...SELECT Syntax
1
2
INSERT INTO [<schema name>.]<table name> [(<column list>)]
<select statement>
INSERT INTO...SELECT Example
1
2
3
4
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-01-01'

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.

Upsert Example
1
2
3
4
INSERT INTO example.employee_backup /* KI_HINT_UPDATE_ON_EXISTING_PK */
SELECT *
FROM example.employee
WHERE hire_date >= '2000-01-01'

Important

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.

UPDATE

Updates can set columns to specified constant values or expressions.

UPDATE Syntax
1
2
3
4
5
6
7
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
1
2
3
4
5
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
1
2
3
4
5
6
7
8
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
1
2
3
4
5
6
7
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.

Important

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
1
2
3
4
5
UPDATE  eb
SET     sal = e.sal, manager_id = e.manager_id
FROM    example.employee_backup eb
INNER JOIN example.employee e
ON eb.id = e.id

Tip

Simplified JOIN syntax is also supported:

UPDATE with JOIN Clause Alternate Example
1
2
3
4
UPDATE  eb
SET     sal = e.sal, manager_id = e.manager_id
FROM    example.employee_backup eb, example.employee e
WHERE   eb.id = e.id

DELETE

Deletes records from a table.

DELETE Syntax
1
2
3
DELETE
FROM [<schema name>.]<table name>
[WHERE <expression list>]

For example, to delete employee with ID 6:

DELETE Example
1
2
3
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
1
2
3
4
5
6
7
8
DELETE
FROM example.employee b
WHERE hire_date =
    (
        SELECT MAX(hire_date)
        FROM example.employee l
        WHERE b.dept_id = l.dept_id
    )