DELETE
This page is part of MariaDB's Documentation.
The parent of this page is: SQL Statements for MariaDB Xpand
Topics on this page:
Overview
Removes rows of data from the given table or tables.
USAGE
Common Syntax:
DELETE FROM <tbl_name> [[AS] <tbl_alias>]
[WHERE <where_condition>]
[ORDER BY ...]
[LIMIT <row_count>]
DELETE
<tbl_name>[.*] [, <tbl_name>[.*]] ...
FROM <table_references>
[WHERE where_condition]
DELETE
FROM <tbl_name>[.*] [, <tbl_name>[.*]] ...
USING <table_references>
[WHERE <where_condition>]
EXAMPLES
DELETE
To delete all data in a table:
DELETE FROM customers;
MariaDB Xpand implements the TRUNCATE statement that drops the original table and re-creates it with the same definition, which is faster than deleting rows one by one.
DELETE .. ORDER BY .. LIMIT
To delete a limited number of rows that match a filter:
DELETE FROM invoices
WHERE invoice_status IN ('canceled', 'unpaid')
ORDER BY invoice_date ASC
LIMIT 1;
DELETE Using Multiple Tables
To delete data by joining information from multiple tables:
DELETE customers.* FROM customers, invoices
WHERE customers.customer_id = invoices.customer_id
AND invoice_status = 'canceled';
The equivalent statement with the USING clause:
DELETE FROM customers USING customers, invoices
WHERE customers.customer_id = invoices.customer_id
AND invoice_status = 'canceled';
Delete Based on the Selected Data
To delete data based on results of a SELECT statement:
DELETE customers, invoices
SET customer_status = 'subscribed', invoice_status = 'paid'
WHERE customers.customer_id = invoices.customer_id
AND invoice_status = 'unpaid'
AND customers.customer_id =
(SELECT c1.customer_id FROM customers AS c1
WHERE c1.customer_email = 'east@example.org');