DELETE QUERY IN SQL
DELETE query in sql deletes all rows matching the search condition from a table.
The DELETE statement removes selected rows of data from a single table. The FROM clause specifies the target table containing the rows. The WHERE clause specifies which rows of the table are to be deleted.
Example 1:
The following deletes the employee number 1234 from the Employee table.
DELETE FROM Employee WHERE EmpNo = ‘1234’;
Example 2:
The following deletes all employee names that begin with “John” from the Employee table.
DELETE FROM Employee WHERE EmpName LIKE ‘John%’
Deleting All Rows:
Omit the WHERE clause to remove all rows from a table:
DELETE FROM Employee;
Deleting from Views and Subqueries:
All platforms allow deletes from views, but with restrictions. Oracle and DB2 allow deletes from a subquery (also known as an inline view). For example, to delete any department not referenced by the employee table, you can specify:
DELETE FROM (
SELECT * FROM departments d
WHERE d.dno NOT IN (
SELECT DISTINCT emp.dno FROM employee emp));
Various restrictions are placed on deletions from views and subqueries because, ultimately, a database system must be able to resolve a DELETE against a view or a subquery to a set of rows in an underlying table.