In this article, we are going to learn how we can update and delete data using Delete and Update statements. In my previous articles, we have learned how we can insert data using INSERT statement, and sort and filter data using WHERE and ORDER BY statements.

To demonstrate UPDATE and DELETE statements, I have restored the sakila database on MySQL Server. We are going to use the film and language table of the sakila database.

MySQL UPDATE Statement

The UPDATE is used to update the values of one or multiple columns of a table. The syntax is the following:

In this syntax, after the UPDATE keyword,

1. We must specify the name of the table that you want to update
2. After the SET keyword, you must specify the name of the column and the new value. If we want to update multiple columns, then each column and values must be separated with a comma
3. In WHERE condition, you must specify the condition. The update statement changes the values based on the condition defined in the WHERE clause. If we do not specify any condition in the WHERE clause, it updates all the values of the specified column

The Update statement has the following two modifiers:

1. The LOW_PRIORITY modifier
2. The IGNORE modifier

Low_Priority modifier

The LOW_PRIORITY modifier is used when you want to delay the execution of the UPDATE query. When you specify the LOW_PRIORITY modifier, the query waits until all the SELECT queries are executed. The storage engines that use the table-level locking (Memory, MyISAM, and MERGE) can use this modifier.

IGNORE modifier

When we specify the IGNORE modifier in the update statement, the execution of the UPDATE statement continues even after it encounters an error. Suppose the UPDATE statement encounters an error that was raised due to unique constraint (duplicate value error) or data conversion errors. This one is helpful when you are running an UPDATE statement that changes the values of a large number of rows.

Now, let us see some examples of the MySQL Update Statement.

Example 1: Change the value of a single column

Suppose we want to change the rating from G to NC-17 of the movie title ACE GOLDFINGER. Execute the following query to view the current values of the rating column.

Query result:

Execute the following query to update values:

To verify that values have been updated, run the following query:

Query result:

As you can see, the rating has been changed.

Example 2: Change the value of multiple columns

Suppose we want to change the replacement cost from 12.99 to 15 and length from 48 to 50 of the movie title AGENT TRUMAN. To change the values, execute the following query to view the current data.

Query Result:

The new value of the replacement_cost column will be 15, and the value of the length column will be 200. To update the values, run the following query:

To verify that values have been updated, run the following query:

Query result:

As you can see, the values have been changed.

Example 3: Change the value using sub-query

In the UPDATE statement, we can also change the value of the column based on the output of the sub-query. Suppose we want to change the value of the language_id of a movie title AGENT TRUMAN without using a static value. Execute the following query to view the current value of the language_id column.

The output is the following:

As you can see, the value of the language_id column is 1 (English). The new value of the language_id column is 2 (Italian). To do that, execute the following query.

To verify that the language_id has been changed, run the below query.

Output:

MySQL DELETE Statement

The MySQL DELETE statement is used to remove the data from the MySQL table. The syntax is the following:

In the syntax,

1. After FROM keyword, you must specify the name of the table from which you want to delete the data
2. After the WHERE clause, you must specify the condition. The query will delete the data that matches the condition. If you do not specify the condition in the WHERE clause, the query will remove all data of the table

Important notes about DELETE statements

1. If you want to delete data of the entire table, instead of using the DELETE statement, you should use the TRUNCATE TABLE statement. The TRUNCATE TABLE is faster than DELETE Statement
2. When the DELETE statement is executed, it removes the data as well as returns the number of the rows that have been deleted
3. You try to delete the data from the table that has a foreign key, and the referential action is ON DELETE CASCADE, then the DELETE statement removes data from the parent and child table. If the referential action is ON DELETE RESTRICT, then the DELETE statement will not delete data from the parent and child table
4. Just like UPDATE Query, DELETE also logs the changes in the Binary Logs. Binary logs are like transactional logs (SQL Server). I will explain more about them in my upcoming articles
5. Once deleted, we can not recover the data from the table, so you should run the delete query between BEGIN Transaction and End Transaction
6. If you delete all the records from the table that has an auto_increment column using DELETE FROM Query, the auto-increment sequence will be reset and starts over again. This behavior is not applicable on MyISAM and InnoDB storage engines

The DELETE statement has the following modifiers:

1. The LOW_PRIORITY modifier
2. QUICK modifier
3. The IGNORE modifier

Low_Priority modifier

The LOW_PRIORITY modifier is used when you want to delay the execution of the DELETE query. When you specify the LOW_PRIORITY modifier, the query waits until all the SELECT queries are executed.

QUICK Modifier

The QUICK modifier can be used in MyISAM tables. When this modifier is used, the index leaves do not merge by the storage engine. This helps to improve the speed of the delete operation.

IGNORE modifier

When we specify the IGNORE modifier in the update statement, the execution of the DELETE statement continues even after it encounters an error. This one is helpful when you are running a DELETE statement that changes the values of a large number of rows.

Now, let us see a few examples of the DELETE statement.

Example 1: Delete all the records from the table

Suppose, we want to delete all the data from the language table. To do that, execute the following query:

The output will show the number of records deleted.

Example 2: Delete a specific record from a table

Suppose we want to delete a movie title whose name is ‘AGENT TRUMAN’ from the film table. To do that, execute the following query:

The output will show the number of records deleted.

Example 3: Delete the rows using LIMIT

Suppose we want to limit the number of records to be deleted from the table. To do that, we can use the LIMIT clause. This method is useful when we want to perform the delete operation in batches. Suppose we want to delete only 50 records from the film table. To do that, execute the following query:

The following is the output:

Summary

In this article, we have learned about UPDATE and DELETE statements of MySQL and the various use cases and examples of it.

Table of contents

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

• Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
• Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
• Changing the location of FILESTREAM data files in SQL Database - October 14, 2022