Table updates

Table updates mk Thu, 06/30/2011 - 16:01

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 attributes in the table definition. Otherwise the attribute-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. MonetDB/SQL doest not support inserts through view definitions.

INSERT INTO table_name [ '(' column_name ',' ... ')' ] [ VALUES '('value_expression','...')' ',' ... | select_query ]

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 table_name SET assignment ',' ... [WHERE search_condition ]

assignment : column_name '='  ( scalar_exp  | search_condition | NULL )




Data deletions

DELETE FROM table_name [WHERE search_condition ]

MonetDB/SQL doest not support deletions through views. Deletion of ALL records  also garbage collects the disk-space of the table. It is similar to a TRUNCATE operation in other systems.