Update Query To Update Rows in MySQL

This tutorial is part of the series Learn Basic SQL Queries Using MySQL. In this tutorial, we will discuss SQL queries to update the rows or data of a table in MySQL.

Update Query

The command UPDATE can be used to modify or update rows of a table using the keyword SET. In case you are remotely logged in to the database, you will also need the UPDATE privilege for the table in order to read the rows from a table.

# UPDATE - Syntax
UPDATE `table_name` SET `column_1` = <value>, `column_2` = <value> ... [WHERE <single or multiple filter conditions>];

Query Explanation

The MySQL command UPDATE can be used to modify rows or data of the given table name where the column name, row value, and name of the table is mandatory. The SET keyword is required in order to set the new column value.

We have to specify at least one column and it's new value while using the UPDATE query. The value must be enclosed in the single quote in case it's a string value.

We also need to specify the conditions to be considered while updating the rows. Using the WHERE clause with the UPDATE command is required where we need to selectively update the rows which is the ideal scenario. We can omit using WHERE while using the UPDATE command, but it will lead to performance issues in case the table data is large.

Examples

This section provides examples to update rows of the table using the UPDATE command. Use the below-mentioned query to create the user table having the id, first name, last name, and active columns to store user data.

# Create the User Table
CREATE TABLE `enterprise`.`user` (
  `user_id` BIGINT NOT NULL,
  `first_name` VARCHAR(45) ,
  `last_name` VARCHAR(45),
  `active` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`user_id`));

The below-mentioned query can be used to insert data in the user table.

# Insert Rows - All Columns
INSERT INTO `user` ( `user_id`, `first_name`, `last_name`, `active` ) VALUES 
    ( 1, 'John', 'Smith', 1 ),
    ( 2, 'Rick', 'Jones', 1 ),
    ( 3, 'Catherine', 'Ponting', 0 ),
    ( 4, 'Harsh', 'Upadhyay', 1 ),
    ( 5, 'Tajwinder', 'Singh', 0 );

The above-mentioned query will insert 5 rows in the table having the id, first name, last name, and active columns to represent 5 different users.

Now we will update the data inserted by us in the user table. It can be done using the UPDATE command as shown below.

# UPDATE - Activate all the inactive users - without key column
UPDATE `user` SET `active` = 1 WHERE `active` = 0;

# Notes - Workbench - You might need to disable safe updates in case the primary key is not used in WHERE clause
SET SQL_SAFE_UPDATES = 0;
UPDATE `user` SET `active` = 1 WHERE `active` = 0;
SET SQL_SAFE_UPDATES = 0;

# Result
1	John		Smith		1
2	Rick		Jones		1
3	Catherine	Ponting		1
4	Harsh		Upadhyay	1
5	Tajwinder	Singh		1


# UPDATE - Deactivate selective users - with key column
UPDATE `user` SET `active` = 0 WHERE `user_id` = 1;
UPDATE `user` SET `active` = 0 WHERE `user_id` IN( 3, 5 );

# Result
1	John		Smith		0
2	Rick		Jones		1
3	Catherine	Ponting		0
4	Harsh		Upadhyay	1
5	Tajwinder	Singh		0

The above-mentioned queries update the user table using the WHERE clause. The table rows will be updated in case the row meets the given conditions.

We can also update multiple column values as shown below.

# UPDATE - Modify first name and last name of first user
UPDATE `user` SET `first_name` = 'Roy', `last_name` = 'Jordan' WHERE `user_id` = 1;

# Result
1	Roy		Jordan		0
2	Rick		Jones		1
3	Catherine	Ponting		0
4	Harsh		Upadhyay	1
5	Tajwinder	Singh		0

This is how we can modify the data stored in the MySQL tables.