DELETE command deletes rows from the table that satisfies the condition provided by its where clause and returns the number of records deleted.
If a DELETE statement without a WHERE clause is issued then, all rows are deleted.
The verb DELETE in SQL is used to remove either:
Ø All the rows from a table
Ø A set of rows from a table
Removal of All Rows
Empty the ACCT_DTLS table
DELETE FROM ACCT_DTLS;
10 rows deleted.
Removal of Specific Row(s)
Remove only the savings bank accounts details from the ACCT_DTLS table.
DELETE FROM ACCT_DTLS WHERE ACCT_NO LIKE ‘SB%’;
6 rows deleted.
Removal of Specific Row(s) Based On The Data Held By The Other Table
Sometimes it is desired to delete records in one table based on values in another table. Since it is not possible to list more than one table in the FROM clause while performing a delete, the EXISTS clause can be used.
Remove the address details of the customer named ‘Ivan’.
DELETE FROM ADDR_DTLS WHERE EXISTS (SELECT FNAME FROM CUST_MSTR
WHERE CUST_MSTR.CUST_NO = ADDR_DTLS.CODE_NO
AND CUST_MSTR.FNAME = "Ivan');
1 row deleted.
This will delete all records in the ADDR_DTLS table where there is a record in the CUST_MSTR table whose FNAME is ‘Ivan’, and the CUST_NO field belonging to the table CUST_MSTR is the same as the CODE_NO belonging to the table ADDR_DTLS.