DELETE

Deletes the rows that match a condition.

Syntax

DELETE FROM <table> [WHERE <condition>]

WHERE takes the same conditions as SELECT, subqueries included. Without WHERE, every row is deleted.

Examples

DELETE FROM tickets WHERE id = 3;

DELETED 1

DELETE FROM tickets WHERE passenger_id IN (SELECT id FROM passengers WHERE member = 0);

How it works

• A delete marks the row as deleted by your transaction. The row’s bytes stay in place, so a transaction that started earlier still sees it.

• The deleted row’s primary key stays taken. Inserting a row with the same key is refused until PURGE frees it:

  kdbs> INSERT INTO tickets VALUES (3, 1, 3, '14C', 20, 0, 'rebooked');
                                      ^
  ERR duplicate primary key 3: a row with this key was deleted; PURGE frees its key (page 139 slot 2) (byte 28)
  

• Deleting a parent row that a live child row still references is refused with FK_VIOLATION:

  kdbs> DELETE FROM flights WHERE id = 1;
  ERR FK_VIOLATION retryable=0 row id=1 is still referenced by 'tickets.flight_id'
  

• Deletes are never refused by an assertion: assertions are upper bounds, and a delete only lowers a group’s count or sum. The group’s running total is updated.

• A WHERE literal of the wrong type for its column is refused when the statement is compiled, so nothing is deleted. It does not answer DELETED 0:

  kdbs> DELETE FROM tickets WHERE baggage_kg = '23';
                                                 ^
  ERR column 'baggage_kg' literal at byte 39: expects an integer literal - unquoted, with no fraction
  

  See SELECT.

• Indexes and Cabins are not changed by a delete. Every read checks their entries against the row, so a deleted row is never returned.

• Reply: DELETED <n>.

See also

• PURGE
• Transactions