DELETE statement is used to delete rows of data from a table. From clause is used for specifying the table and WHERE clause is used for specifying which rows have to be deleted.
DELETE FROM EMPLOYEES WHERE NAME = ‘Sarah Jones’
The above query deletes a row for employee Sarah Jones.
Searched delete: WHERE clause searches for the condition and for every true, a row is deleted.
DELETE FROM ORDERS WHERE ORDER_DATE < ’11-SEP-12′
Delete all rows: To delete all rows of a table, simply don’t put any WHERE clause. By this process all rows of a table will be deleted. However, the actual table name still remains in the database and new rows can be inserted to this table.
DELETE FROM EMPLOYEES
The above query will delete all rows from the EMPLOYESS table.
DELETE with subquery: Join operation can’t be used with a DELETE statement. To overcome such a situation DELETE is used with a subquery.
DELETE FROM ORDERS, EMPLOYEES WHERE EMP = EMPL_NUM AND NAME = ‘Sarah Jones’
The above query is an incorrect query. To write it correctly, DELETE has to be used with a subquery:
DELETE FROM ORDERS WHERE EMP = (SELECT EMPL_NUM FROM EMPLOYESS WHERE NAME = ‘Sarah Jones’)
The above query deletes all orders of Sarah Jones.