MariaDB: UPDATE Statement

This MariaDB tutorial explains how to use the MariaDB UPDATE statement with syntax and examples.

Description

The MariaDB UPDATE statement is used to update existing records in a table. There are 3 syntaxes for the update query in MariaDB depending on the type of update that you wish to perform.

Syntax

The syntax for the MariaDB UPDATE statement when updating one table is:

UPDATE table
SET column1 = expression1,
    column2 = expression2,
    ...
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]]
[LIMIT number_rows];

OR

The syntax for the MariaDB UPDATE statement when updating one table with data from another table is:

UPDATE table1
SET column1 = (SELECT expression1
               FROM table2
               WHERE conditions)
[WHERE conditions];

OR

The syntax for the MariaDB UPDATE statement when updating multiple tables is:

UPDATE table1, table2, ... 
SET column1 = expression1,
    column2 = expression2,
    ...
WHERE table1.column = table2.column
AND conditions;

Parameters or Arguments

column1, column2

The columns that you wish to update.

expression1, expression2

The new values to assign to the column1, column2. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.

WHERE conditions

Optional. The conditions that must be met for the update to execute.

ORDER BY expression

Optional. It may be used in combination with LIMIT to sort the records appropriately when limiting the number of records to be updated.

LIMIT number_rows

Optional. If LIMIT is provided, it controls the maximum number of records to update in the table. At most, the number of records specified by number_rows will be update in the table.

Example - Update single column

Let's look at how to use the UPDATE statement to update one column in a table in MariaDB.

For example:

UPDATE sites
SET site_name = 'AODBA.com'
WHERE site_name = 'mySite.com';

This UPDATE example would update the site_name to 'AODBA.com' in the sites table where the site_name is 'mySite.com'.

Example - Update multiple columns

Let's look at a how to update more than one column using a single UPDATE statement in MariaDB.

For example:

UPDATE sites
SET site_name = 'AODBA.com',
    server_name = 'MyServer'
WHERE site_name = 'mySite.com';

You can update multiple columns in MariaDB by separating the column/value pairs with commas.

This UPDATE statement example would update the site_name to 'AODBA.com' and the server_name to 'MyServer' where the site_name is 'mySite.com'.

Example - Update table with data from another table

Let's look at how to update a table with data from another table in MariaDB using the UPDATE statement.

For example:

UPDATE pages SET site_id = (SELECT site_id                FROM sites                WHERE site_name = 'AODBA.com') WHERE page_id = 15;

This UPDATE example would update only the pages table for all records where the page_id is less than or equal to 15. When the site_name is 'AODBA.com', the site_id from the sites table would be copied to the site_id field in the pages table.

Example - Update multiple Tables

Let's look at how to perform an update that involves more than one table in a single UPDATE statement in MariaDB.

For example:

UPDATE sites, pages
SET sites.server_name = pages.host_name
WHERE sites.site_id = pages.site_id
AND sites.site_id > 500;

This MariaDB UPDATE example would update the server_name field in the sites table to the host_name field from the pages table. This update would only be performed when the site_id in the sites table is greater than 500 and the site_id field from the sites table matches the site_id from the pages table.