SQL DELETE

SQL DELETE

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.

SYNTAX

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.

customer_idfirst_namelast_namephone_numbercitystatezip_code
1JamesMason999-999-9999Jersey CityNJ12345
2PeterClark888-888-8888JuneauAK99801
3MaryWilliams777-777-7777IrvingTX75038
4JimWarner666-666-6666QuincyMA02169



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’;

or

 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

customer_idfirst_namelast_namephone_numbercitystatezip_code
1JamesMason999-999-9999Jersey CityNJ12345
3MaryWilliams777-777-7777IrvingTX75038
4JimWarner666-666-6666QuincyMA02169

 

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.

 

 

Leave A Reply

Your email address will not be published. Required fields are marked *