Skip to main content

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

MonetDB/SQL doest 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 [ schema_name '.' ] table_name
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.

MonetDB/SQL doest not support data updates on views.

Data deletions

DELETE FROM [ schema_name '.' ] table_name
[ WHERE search_condition ]

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

MonetDB/SQL doest not support data deletions on views.

 

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

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 doest not support truncations of data on views.