UPDATE

Overview

Sets new values on rows in a table or tables.

USAGE

Common Syntax:

UPDATE <table_references>
   SET <col_name> = {<value> | DEFAULT} [, <col_name> = {<value> | DEFAULT}] ...
   [WHERE <where_condition>]
   [ORDER BY ...]
   [LIMIT <row_count>]

EXAMPLES

UPDATE

Demonstrating the before and after state for an update:

SELECT * FROM customers WHERE customer_id = 8;
+-------------+---------------+-------------------+-----------------+
| customer_id | customer_name | customer_email    | customer_status |
+-------------+---------------+-------------------+-----------------+
|           8 | North LLC     | north@example.net | active          |
+-------------+---------------+-------------------+-----------------+
UPDATE customers SET customer_status = 'inactive' WHERE customer_id = 8;
SELECT * FROM customers WHERE customer_id = 8;
+-------------+---------------+-------------------+-----------------+
| customer_id | customer_name | customer_email    | customer_status |
+-------------+---------------+-------------------+-----------------+
|           8 | North LLC     | north@example.net | inactive        |
+-------------+---------------+-------------------+-----------------+

Multi-Table UPDATE

To update data in multiple tables:

UPDATE customers, invoices
   SET customer_status = 'unsubscribed', invoice_status = 'canceled'
   WHERE invoices.customer_id = customers.customer_id
      AND invoice_status = 'unpaid'
      AND DATEDIFF(NOW(), invoice_date) > 30;

Update Based on the Selected Data

To update data based on results of a SELECT statement:

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

CHANGE HISTORY

Release Series

History

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.