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. Show
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 StatementThe 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,
The Update statement has the following two modifiers:
Low_Priority modifierThe 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 modifierWhen 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 columnSuppose 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 columnsSuppose 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-queryIn 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 StatementThe MySQL DELETE statement is used to remove the data from the MySQL table. The syntax is the following:
In the syntax,
Important notes about DELETE statements
The DELETE statement has the following modifiers:
Low_Priority modifierThe 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 ModifierThe 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 modifierWhen 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 tableSuppose, 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 tableSuppose 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 LIMITSuppose 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:
SummaryIn 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 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 How do you delete and update data in MySQL?MySQL DELETE Statement. DELETE FROM table_name WHERE condition;. Example. DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';. DELETE FROM table_name;. Example. DELETE FROM Customers;. How do you delete and update in SQL?After creating and populating a table in SQL, the UPDATE command can be used to change the value of a column. If there is no WHERE , it will change the value of all the rows in the table, so make sure you restrict what it updates with a correct WHERE condition. The DELETE command can be used to delete rows.
How do I update something in MySQL?MySQL UPDATE. First, specify the name of the table that you want to update data after the UPDATE keyword.. Second, specify which column you want to update and the new value in the SET clause. ... . Third, specify which rows to be updated using a condition in the WHERE clause.. What is the Delete command in MySQL?DELETE is a DML statement that removes rows from a table. A DELETE statement can start with a WITH clause to define common table expressions accessible within the DELETE .
|