Delete Query in SQL

DELETE statements are used to delete records from the tables. Similar to SELECT and UPDATE statements, in this case also, we need to specify correct WHERE clause to delete the correct data. Otherwise it will delete entire records from the table or other correct data from the table.

Consider the query to delete records from BOOK_LOAN, who has borrowed books on 10^th May 2015. We have to query on BOOK_LOAN table to get the records which has LOAN_DATE as 10^th May and then delete the records.

DELETE FROM BOOK_LOAN
WHERE LOAN_DATE = ’10-May-2015’;

Suppose we have not specified any conditions in above DELETE statement. Then it would delete the all records from the table.

Suppose second copy of the book, ‘The Essence Of Databases’ is lost and needs to be deleted from BOOK_COPY table, so that it should not show up in the availability list. It is clear from this statement that we have to delete the 2nd copy entry from BOOK_COPY table. But it has only ISBN, not book name. Hence we need to first get the ISBN from the BOOKS table for the given book name and then delete the record from BOOK_COPY table. Hence our delete statement to delete the records would be :

DELETE FROM BOOK_COPY
WHERE COPY_NUM = 2
AND ISBN = (SELECT ISBN FROM BOOKS 
		WHERE BOOK_NAME = ‘The Essence Of Databases’);

One can specify any condition in the WHERE clause of DELETE statement to delete the records. We can even specify range of values and get the records deleted.

Consider the query to delete the records for which books are received in library prior to 2014. That means any book copies entered into table before 1-Jan-2014 needs to be deleted from the table.

DELETE FROM BOOK_COPY WHERE RECEIVED_DATE<’1-Jan-2014’;