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:

idnamecity
1JamesNew York
2TonyDelhi
3ChrisDubai
users

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:

idnamecity
1JamesNew York
2TonyDelhi
3ChrisLondon
users

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;
idnamecity
1TomOman
2TonyDelhi
3ChrisLondon
users

If we don’t specify the where clause, all rows of the users table will be updated.

UPDATE users
    SET 
    city = 'Delhi';
idnamecity
1Tom Delhi
2TonyDelhi
3Chris Delhi
users

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.

Adarsh Kumar

I am an engineer by education and writer by passion. I started this blog to share my little programming wisdom with other programmers out there. Hope it helps you.

Leave a Reply