How to UPDATE / DELETE in MySQL?
If we need to update or remove any records from our table we can use an UPDATE or DELETE statement. We have the ‘students’ table created as in MySQL SELECT Statement. We will use the same table and see how we can UPDATE / DELETE in MySQL.
UPDATE statement:
If we want to update any particular columns for particular record sets we can do it by using the SQL UPDATE command. Note that if we try to update any column(s) without anywhere conditions, it will update that column value for all the records present in the table.
Syntax:
UPDATE schema.table_name
SET column_name1 = value1, column_name2 = value2, …
WHERE condition1, condition2 …;
Example 1:
As shown in the below image we have a record in the ‘students’ table for roll number ‘5’. We will update the marks of the students to 480.
Query
UPDATE students SET marks = 480 WHERE roll_num = 5;
Once successfully updated we will get a success message as highlighted below. So if we select the same data again from the table we will get the updated record. We can also use MySQL operators (AND, OR, NOT) in the where the condition of update.
Example 2:
For the following student’s data we will update marks and gender by using name and class.
| roll_num | name | marks | class | gender |
| 3 | BBH | 390 | 1 | F |
Query:
UPDATE students SET marks = 480, gender = ‘M’ WHERE name = “BBH” AND class = “1”;
DELETE statement:
If we want to delete any record instead of updating, we can use the delete SQL command on that particular table with conditions. Note that deleting without conditions will delete all the records from the table mentioned.
Syntax:
DELETE FROM schema.table_name WHERE condition1, condition2 …;
Example 1:
We will delete the record whose roll number is 21.
Query:
DELETE FROM students where roll_num = 21;
Example 2: Delete with multiple conditions.
We have 3 records for the name ‘PJS’, we will try to delete the record from the 3rd class.
Query:
DELETE FROM students WHERE name = “PJS” AND class = 3;
Records after deletion will be as below.
Getting Update/delete Error:
If we get the error in MySQL workbench while deleting or updating any records then we need to disable the SQL safe update settings.
We will get the error below if the safe update is enabled.
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
To disable the safe update settings go to Edit → Preferences as shown below.
We will get the Workbench Preferences window open.
Select ‘SQL Editor’ and then scroll down to the bottom as shown in the 2nd image below. We will able to see the checkbox for ‘Safe Updates’ we need to uncheck that checkbox and then click on ‘Ok’.
Once done Disconnect and reconnect again to the database and try to update/delete.
