Learn MySQL: Delete and Update Statements

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:

Change value of one column current value

Execute the following query to update values:

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

Query result:

Change value of one column new value

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:

Change value of multiple column current value

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:

Change value of multiple column new value

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:

Change value using sub-query current value

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:

Change value using sub-query new value

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.

Delete all records

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.

Delete specific record

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:

Delete with limit clause

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

Nisarg Upadhyay
Latest posts by Nisarg Upadhyay (see all)

Author: admin

Leave a Reply

Your email address will not be published.