DELETE¶

You can delete records in the table by using the DELETE statement. You can specify delete conditions by combining the statement with the WHERE Clause. You can delete one or more tables with one DELETE statement.

<DELETE single table>
DELETE [FROM] [schema_name.]table_name [ WHERE <search_condition> ] [LIMIT row_count]

<DELETE multiple tables FROM ...>
DELETE [schema_name.]table_name [{, [schema_name.]table_name}] FROM <table_specifications> [ WHERE <search_condition> ]

<DELETE FROM multiple tables USING ...>
DELETE FROM [schema_name.]table_name [{, [schema_name.]table_name}] USING <table_specifications> [ WHERE <search_condition> ]

<table_specifications>: You can specify the statement such as FROM clause of the SELECT statement and one or more tables can be specified.
schema_name: Specifies the schema name. If omitted, the schema name of the current session is used.
table_name: Specifies the name of a table where the data to be deleted is contained. If the number of table is one, the FROM keyword can be omitted.
search_condition: Deletes only data that meets search_condition by using WHERE Clause. If it is specified, all data in the specified tables will be deleted.
row_count: Specifies the number of records to be deleted in the LIMIT Clause. It can be one of unsigned integer, a host variable or a simple expression.

When a table to delete records is only one, LIMIT Clause can be specified. You can limit the number of records by specifying the LIMIT Clause. If the number of records satisfying the WHERE Clause exceeds row_count, only the number of records specified in row_count will be deleted.

Note

On the DELETE statement with multiple tables, the table alias can be defined within <table_specifications> only. At the outside of <table_specifications>, the table alias defined in <table_specifications> can be used.
Previous versions of CUBRID 9.0 allow only one table for <table_specifications>.

CREATE TABLE a_tbl(
    id INT NOT NULL,
    phone VARCHAR(10));
INSERT INTO a_tbl VALUES(1,'111-1111'), (2,'222-2222'), (3, '333-3333'), (4, NULL), (5, NULL);

--delete one record only from a_tbl
DELETE FROM a_tbl WHERE phone IS NULL LIMIT 1;
SELECT * FROM a_tbl;

           id  phone
===================================
            1  '111-1111'
            2  '222-2222'
            3  '333-3333'
            5  NULL

--delete all records from a_tbl
DELETE FROM a_tbl;

Below tables are created to explain DELETE JOIN.

CREATE TABLE a_tbl(
    id INT NOT NULL,
    phone VARCHAR(10));
CREATE TABLE b_tbl(
    id INT NOT NULL,
    phone VARCHAR(10));
CREATE TABLE c_tbl(
    id INT NOT NULL,
    phone VARCHAR(10));

INSERT INTO a_tbl VALUES(1,'111-1111'), (2,'222-2222'), (3, '333-3333'), (4, NULL), (5, NULL);
INSERT INTO b_tbl VALUES(1,'111-1111'), (2,'222-2222'), (3, '333-3333'), (4, NULL);
INSERT INTO c_tbl VALUES(1,'111-1111'), (2,'222-2222'), (10, '333-3333'), (11, NULL), (12, NULL);

The below queries delete rows after joining multiple tables. They show the same result.

-- Below four queries show the same result.
--  <DELETE multiple tables FROM ...>

DELETE a, b FROM a_tbl a, b_tbl b, c_tbl c
WHERE a.id=b.id AND b.id=c.id;

DELETE a, b FROM a_tbl a INNER JOIN b_tbl b ON a.id=b.id
INNER JOIN c_tbl c ON b.id=c.id;

-- <DELETE FROM multiple tables USING ...>

DELETE FROM a, b USING a_tbl a, b_tbl b, c_tbl c
WHERE a.id=b.id AND b.id=c.id;

DELETE FROM a, b USING a_tbl a INNER JOIN b_tbl b ON a.id=b.id
INNER JOIN c_tbl c ON b.id=c.id;

For more details on join syntax, see Join Query.