Using INSERT, UPDATE, and DELETE
DML statements INSERT
, UPDATE
, and DELETE
perform the same functions that they do in any ACID compliant database. INSERT
is typically used to load individual rows into physical memory or load a table using an INSERT AS SELECT
clause. UPDATE
and DELETE
are used to modify the data.
You can intermix INSERT
, UPDATE
, and DELETE
. 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. If you cancel a DML statement, Vertica rolls back the statement.
Vertica differs from traditional databases in two ways:
DELETE
does not actually delete data from disk storage; it marks rows as deleted so they are available for historical queries.UPDATE
writes two rows: one with new data and one marked for deletion.
Unless you specify otherwise, INSERT
, UPDATE
and DELETE
statements write data to the WOS. On overflow, the statements write data to the ROS.