Data Manipulation (DML)

INSERT

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

• Inserting values
• Copying data from another table

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.

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

1
2

INSERT INTO [<schema name>.]<table name> [(<column list>)]
VALUES (<column value list>)[,...]

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')

1
2

INSERT INTO [<schema name>.]<table name> [(<column list>)]
<select statement>

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'

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'

UPDATE

Updates can set columns to specified constant values or expressions.

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:

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:

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:

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.

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:

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

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.

1
2
3

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

For example, to delete employee with ID 6:

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:

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
    )