Site icon Narayana Tutorial

SQL DELETE

SQL DELETE OPERATIONS

The DELETE command deletes rows from the table that satisfies the condition provided by its WHERE clause and returns the number of records are deleted. If the DELETE command without a WHERE clause is issued then, all rows are deleted. We can retrieve the deleted records back if we had not executed COMMIT command after deletion of data by using ROLLBACK command. If we used DELETE command to delete records, we can retrieve deleted records by using ROLLBACK command, If we used TRUNCATE command to delete records, we can retrieve truncated records by using ROLLBACK command.

DELETE set of rows

Syntax

DELETE FROM <TableName> WHERE <ColumnName 1> = <Condition>

Example

DELETE FROM EMPLOYEE WHERE EMPLOYEE_ID=13;

DELETE all rows

Syntax

DELETE FROM <TableName>

Example

DELETE FROM EMPLOYEE

DELETE specific rows based on the data held by the other table

Syntax

DELETE FROM <TableName 1> tab1  WHERE EXISTS(SELECT <ColumnName 1> FROM <TableName 2> tab2 
WHERE tab1.<ColumnName 1>=tab2.<ColumnName 1>

Example

DELETE FROM EMPLOYEE_HISTORY tab1  WHERE EXISTS(SELECT FIRSTNAME FROM EMPLOYEE tab2 
WHERE tab1.ID=tab2.ID);