Using INSERT, UPDATE, and DELETE
DML statements INSERT
, UPDATE
, and DELETE
perform the same functions that they do in any ACID-compliant database:
INSERT
loads individual rows into physical memory or loads a table using anINSERT AS SELECT
clause.UPDATE
andDELETE
modify existing data.
By default, INSERT
, UPDATE
, and DELETE
statements write data to the WOS. If the WOS is full, these statements write data directly to the ROS. However, if you use the TRICKLE keyword, if the WOS is full, Vertica reports an error and rolls back the entire data load.
For large INSERT
or UPDATE
operations, use the DIRECT
hint. This hint forces Vertica to load all data directly to ROS. For performance reasons, Vertica does not recommend loading large number of rows as single row inserts. Instead, use COPY
.
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.
You can intermix INSERT
, UPDATE
, and DELETE
operations. Vertica follows the SQL-92 transaction model. 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.