MySQL UPDATE - Update Records of Table

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.