Summary: In this tutorial, we will learn to update a table in MySQL using the UPDATE TABLE statement.
Introduction to MySQL UPDATE statement
The UPDATE statement in MySQL updates existing records (rows) of a table in the database.
Using the statement, we can easily update one or more columns of single or multiple rows of a MySQL table.
The general syntax of the MySQL UPDATE statement is:
UPDATE [LOW_PRIORITY] [IGNORE] table_name
SET
column_name_1 = value_1,
column_name_2 = value_2,
...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count];
Note: The syntaxes within the square brackets are optional.
The above statement updates the records of the specified columns of the table table_name
, where:
- the
WHERE
clause, if specified, identifies the rows that needs to be updated, - the
ORDER BY
clause, if specified, decides the order in which the rows will be updates, - and the
LIMIT
clause, if specified, places a limit on the number of rows that can be updated.
The LOW_PRIORITY
and IGNORE
are the optional modifiers for the SQL update statement.
The LOW_PRIORITY
modifier delays the execution of the statement until no other clients are reading from the same table, whereas the IGNORE
modifier forces the update statement to execute even if errors occur during the update.
MySQL UPDATE Examples
Let’s say the users
table in the database has the following entries:
id | name | city |
---|---|---|
1 | James | New York |
2 | Tony | Delhi |
3 | Chris | Dubai |
If we have to update the city of Chris then the update statement for it will be as follows:
UPDATE users
SET
city = 'London'
WHERE name = 'Chris';
After the execution the users
table will look like this:
id | name | city |
---|---|---|
1 | James | New York |
2 | Tony | Delhi |
3 | Chris | London |
Similarly, if we want to update both name and city of a user, we need to select the user by id.
UPDATE users
SET
name = 'Tom'
city = 'Oman'
WHERE id = 1;
id | name | city |
---|---|---|
1 | Tom | Oman |
2 | Tony | Delhi |
3 | Chris | London |
If we don’t specify the where clause, all rows of the users
table will be updated.
UPDATE users
SET
city = 'Delhi';
id | name | city |
---|---|---|
1 | Tom | Delhi |
2 | Tony | Delhi |
3 | Chris | Delhi |
Conclusion
The UPDATE statement in MySQL is used to update one or more columns of single or multiple rows in a table.
Because it modifies data in the table, it is a DML (Data Manipulation Language) statement.