MySQL: UPDATE Statement

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

Description

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

Syntax

In its simplest form, the syntax for the UPDATE statement when updating one table in MySQL is:

UPDATE table
SET column1 = expression1,
    column2 = expression2,
    ...
[WHERE conditions];

However, the full syntax for the MySQL UPDATE statement when updating one table is:

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

OR

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

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

OR

The syntax for the MySQL 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

LOW_PRIORITY

Optional. If LOW_PRIORITY is provided, the update will be delayed until there are no processes reading from the table. LOW_PRIORITY may be used with MyISAM, MEMORY and MERGE tables that use table-level locking.

IGNORE

Optional. If IGNORE is provided, all errors encountered during the update are ignored. If an update on a row would result in a violation of a primary key or unique index, the update on that row is not performed.

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 a very simple MySQL UPDATE query example.

UPDATE customers
SET last_name = 'Mark'
WHERE customer_id = 5000;

This MySQL UPDATE example would update the last_name to 'Mark' in the customers table where the customer_id is 5000.

Example - Update multiple columns

Let's look at a MySQL UPDATE example where you might want to update more than one column with a single UPDATE statement.

UPDATE customers
SET state = 'California',
    customer_rep = 32
WHERE customer_id > 100;

When you wish to update multiple columns, you can do this by separating the column/value pairs with commas.

This MySQL UPDATE statement example would update the state to 'California' and the customer_rep to 32 where the customer_id is greater than 100.

Example - Update table with data from another table

Let's look at an UPDATE example that shows how to update a table with data from another table in MySQL.

UPDATE customers SET city = (SELECT city             FROM suppliers             WHERE suppliers.supplier_name = customers.customer_name) WHERE customer_id > 2000;

This UPDATE example would update only the customers table for all records where the customer_id is greater than 2000. When the supplier_name from the suppliers table matches the customer_name from the customers table, the city from the suppliers table would be copied to the city field in the customers table.

Example - Update multiple Tables

Let's look at a MySQL UPDATE example where you might want to perform an update that involves more than one table in a single UPDATE statement.

UPDATE customers, suppliers
SET customers.city = suppliers.city
WHERE customers.customer_id = suppliers.supplier_id;

This MySQL UPDATE statement example would update the city field in the customers table to the city from the suppliers table where the customer_id matches the supplier_id.