UPDATE
This page is part of MariaDB's Documentation.
The parent of this page is: SQL Statements for MariaDB Xpand
Topics on this page:
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');