Using INSERT, UPDATE, and DELETE
INSERTloads individual rows into physical memory or loads a table using an
INSERT AS SELECTclause.
DELETEmodify existing data.
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.
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
Vertica differs from traditional databases in two ways:
DELETEdoes not actually delete data from disk storage; it marks rows as deleted so they are available for historical queries.
UPDATEwrites two rows: one with new data and one marked for deletion.
You can intermix
DELETE operations. Vertica follows the SQL-92 transaction model. You do not have to explicitly start a transaction, but you must use a
ROLLBACK command (or
COPY) to end a transaction. If you cancel a DML statement, Vertica rolls back the statement.