DELETE OPERATIONS

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

OR

Ø  A set of rows from a table

Removal of All Rows

Syntax:

DELETE FROM ;

Example:

Empty the ACCT_DTLS table

DELETE FROM ACCT_DTLS;

Output:

10 rows deleted.

Removal of Specific Row(s)

Syntax:

DELETE FROM WHERE ;

Example :

Remove only the savings bank accounts details from the ACCT_DTLS table.

DELETE FROM ACCT_DTLS WHERE ACCT_NO LIKE ‘SB%’;

Output:

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.

Example :

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');

Output:

1 row deleted.

Explanation:

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.