DELETE

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>]

DETAILS

SYNONYMS

SCHEMA

PARAMETERS

SKYSQL

PRIVILEGES

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');

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

23.09

  • Present starting in MariaDB Xpand 23.09.1.

6.1

  • Present starting in MariaDB Xpand 6.1.0.

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.1

  • Present starting in MariaDB Xpand 6.1.0.

EXTERNAL REFERENCES