The DELETE operation is used for deleting rows in a table. You can either delete one row at a time or multiple rows using the DELETE query.
The syntax for DELETE query while trying to delete 1 record or multiple records at a time is –
DELETE from table_name WHERE condition;
Note – Although WHERE clause is optional in the above syntax, if you forget to have the WHERE clause, the whole table will get deleted. It is always a good practice recommended to write a SELECT query with the WHERE clause that you would like to use for the DELETE query and take a look at the rows that you plan to delete and then execute the DELETE query. If no rows are qualified by the WHERE clause, then none of the records would be deleted by the DELETE query.
Illustration – Say if you have a Customer table holding information about customers visiting your store.
Query 1 – If you would like to delete the record of Peter Clark, then you would write a delete query as shown below –
DELETE FROM customer WHERE first_name = ‘Peter’ AND last_name = ‘Clark’;
DELETE FROM customer WHERE customer_id= 2;
Once the above DELETE query is executed, you will notice the record for Peter Clark in the table has been deleted as shown below
Query 2 – If you omit WHERE clause in the DELETE query, then all the rows in the table will be deleted. And if that is not your intention, you are in trouble. So be double sure when you write DELETE queries –
DELETE FROM customer;
Query 3 – If the WHERE clause does not fetch any records then no records will be deleted by the DELETE query –
DELETE FROM customer WHERE first_name = ‘Rhonda’ AND last_name = ‘Phil‘;
0 rows deleted.