Using INSERT, UPDATE, and DELETE

The SQL data manipulation language (DML) commands INSERT, UPDATE, and DELETE perform the same functions that they do in a row-oriented database with the following exceptions:

• UPDATE and DELETE always write tuples to the WOS.
• UPDATE writes two tuples into the WOS: one with new data and one marked for deletion.
• DELETE does not actually delete data from disk storage; it simply marks tuples as deleted so that they can be found by historical queries. The ability to delete data from disk storage will be available in a future release.

You can intermix the INSERT, UPDATE, and DELETE commands. Vertica follows the SQL-92 transaction model. In other words, you do not have to explicitly start a transaction but you must use a COMMIT or ROLLBACK command (or COPY) to end a transaction. Cancelling a DML statement causes the current transaction to roll back.

You cannot use AT EPOCH LATEST or AT TIME in an INSERT ... SELECT statement. To run the query in snapshot isolation mode, use the SET TRANSACTION CHARACTERISTICS statement to set the isolation level to READ COMMITTED.