Table Updates

table-update:
    insert-stmt
  | update-stmt
  | merge-stmt
  | delete-stmt
  | truncate-stmt

Note These table update statements only work on tables, not on views.

Data insertions

A table can be populated using an insert statement. It takes a table name and a value expression list. The expression result types should align with the columns in the table definition. Otherwise the column-value association should be explicitly defined using a column name list.

Multiple rows can be inserted in a single statement.

The result of a query can be bulk inserted into a table, provided both source and destination are type compatible. Insertion of a table into itself effectively doubles its content, provided non of the table constraints is violated.

insert-stmt:
  [ WITH cte_alias AS '(' SELECT_query ')' [',' cte_alias AS '(' SELECT_query ')' ...] ]
  INSERT INTO [ schema_name '.' ] table_name  [ '(' column_name [ ',' ... ] ')' ]
  { VALUES '(' value_expression [ ',' ... ] ')'  [ ',' '(' ... ')' ]
    | SELECT_query }

value_expression: { scalar_exp | DEFAULT | NULL }

You must have INSERT privilege for the table. The creator/owner of the table will have INSERT privilege automatically.

The "WITH cte_alias AS (SELECT_query)" option is also supported.

Data updates

The update statement syntax follows the SQL standard, but its semantics for bulk updates on keys may be slightly different than expected from other systems. In particular, the update implementation ensures that you can freely update any column without the danger of run-away values.

update-stmt:
  [ WITH cte_alias AS '(' SELECT_query ')' [',' cte_alias AS '(' SELECT_query ')' ...] ]
  UPDATE [ schema_name '.' ] table_name  [ [AS] table_alias ]
  SET assignment [ ',' assignment ... ]
  [ WHERE search_condition ]

assignment: column_name '=' { scalar_exp  | search_condition | NULL | DEFAULT }

You must have UPDATE privilege for the table or column(s). The creator/owner of the table will have UPDATE privilege automatically.

The "WITH cte_alias AS (SELECT_query)" option is also supported.

Data merges

See MERGE statement.

Data deletions

delete_stmt:
  [ WITH cte_alias AS '(' SELECT_query ')' [',' cte_alias AS '(' SELECT_query ')' ...] ]
  DELETE FROM [ schema_name '.' ] table_name  [ [AS] table_alias ]
  [ WHERE search_condition ]

The delete statement syntax follows the SQL standard.

You must have DELETE privilege for the table. The creator/owner of the table will have DELETE privilege automatically.

The "WITH cte_alias AS (SELECT_query)" option is also supported.

To quickly delete all rows in a table use TRUNCATE TABLE.

Data table truncate

truncate_stmt:
  TRUNCATE [ TABLE ] [ schema_name '.' ] table_name
  [ CONTINUE IDENTITY | RESTART IDENTITY ]
  [ RESTRICT | CASCADE ]

This statement deletes all rows of a table. It is faster than using DELETE FROM table; statement.

Note In MonetDB it is possible to use TRUNCATE statements in a transaction and thus be able to roll back the effects of a truncate.

You must have TRUNCATE privilege for the table. The creator/owner of the table will have TRUNCATE privilege automatically.

A 'CONTINUE IDENTITY' or 'RESTART IDENTITY' clause can be passed to restart or not an identity sequence if present in the table. Default is to CONTINUE IDENTITY sequence numbering.

The 'CASCADE' option instructs to truncate referencing table(s) also if the referencing table(s) have foreign key references to this table. The default behavior is 'RESTRICT'.

Examples

INSERT INTO country (code, name) VALUES ('NL','The Netherlands'), ('DE','Germany'), ('BE','Belgium');

WITH data(cd, nm) AS (
 SELECT * FROM (VALUES ('ES','Spain'), ('FR','France'), ('PT','Portugal')) as t(c,n)
)
INSERT INTO country (code, name) SELECT cd, nm FROM data;

UPDATE employee SET salary = salary * 1.07, bonus = 1200 WHERE id = 12345;

DELETE FROM web_log WHERE "timestamp" <= {ts '2020-12-23 23:59:59'};

TRUNCATE TABLE mysch.imp_article  CONTINUE IDENTITY  CASCADE;