DELETE

DELETE Command

Syntax

DELETE FROM table_sources [WHERE search_expression]

Description

Deletes the rows of a table or several tables. The table_sources works the same as with the SELECT command and can contain more than one joined table. All matching rows from all tables in the table sources are deleted. If you wish to delete from one table while matching rows from another table, you can use subqueries in the WHERE clause, eg, by using EXISTS.

The WHERE search_expression follows the same syntax as with the SELECT command. It is used to narrow the selection of rows that are deleted.

Example

Multi-table delete

CREATE DATABASE IF NOT EXISTS multi_table_delete;
GO; USE multi_table_delete;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (i INT PARTITION NOT NULL PRIMARY KEY);
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (i INT PARTITION NOT NULL PRIMARY KEY,
                 j INT NOT NULL REFERENCES t1 (i));
GO;
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (3);
INSERT INTO t2 VALUES (1,1);
INSERT INTO t2 VALUES (2,1);
INSERT INTO t2 VALUES (3,2);
GO; USE multi_table_delete;
DELETE FROM t1 JOIN t2 ON t1.i=t2.j WHERE t1.i=2;
SELECT t1.i AS t1,t2.i AS t2 FROM t1 JOIN t2 ON t1.i=t2.j;

Related