Table Updates

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.

update-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. MonetDB/SQL does not support data insertions on views.

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. MonetDB/SQL doest not support updates through views.

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.

MonetDB/SQL does not support data updates on views.

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 ]

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.

MonetDB/SQL does not support data deletions on views. To quickly delete all rows in a table use TRUNCATE TABLE.

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

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

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

MonetDB/SQL does not support truncations of data on views.

Examples

UPDATE employee SET salary = salary * 1.07, bonus = 1200 WHERE id = 12345;
DELETE FROM web.log WHERE "timestamp" <= '2016-12-23 23:59:59';
TRUNCATE TABLE mysch.imp_article CONTINUE IDENTITY CASCADE;