SQL Delete

 
    DELETE FROM table [WHERE expression]
         

This SQL DELETE statement deletes zero or more rows of the specified table. eXtremeSQL deletes are “searched deletes.” Currently, eXtremeSQL does not support “positioned delete” (i.e., the WHERE CURRENT OF cursor_name construction). The "searched delete" deletes all rows of the table that satisfy the WHERE condition.

For example,

     
    DELETE FROM Employee
    or
    DELETE FROM Employee WHERE name LIKE “X%”
     

Note: eXtremeDB and, by extension, eXtremeSQL, do not enforce referential integrity. Take care NOT to delete rows with primary key values for which there are one or more referencing foreign keys.

LIMIT Clause

eXtremeSQL supports the LIMIT clause in DELETE statements. For example:

 
    XSQL>create table foo(x integer primary key);
    XSQL>insert into foo values (4),(5),(2),(1),(3);
    XSQL>delete from foo limit 1;
    XSQL>select * from foo;
    x
    ------------------------------------------------------------------------------
    2
    3
    4
    5
     
    Selected records: 4
 
    XSQL>select * from foo where x < 4 limit 2;
    x
    ------------------------------------------------------------------------------
    2
    3
 
    Selected records: 2
 
    XSQL>delete from foo where x < 4 limit 2;
    XSQL>select * from foo;
    x
    ------------------------------------------------------------------------------
    4
    5
 
    Selected records: 2
     

Note that there is no guarantee that records will be deleted in any particular order. The optimizer's rule is the following:

If the query contains a predicate x<?, x<=?, x>? or x>=? and an ordered (tree) index exists for the "x" field, then the optimizer performs the index search and traverses records in ascending order of "x". Thus if LIMIT N is specified, records with the lowest values of x are removed.

In all other cases the order is not specified and any N of records can be deleted by the DELETE with LIMIT statement.