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 WHEREEXISTS(SELECT <ColumnName 1> FROM <TableName 2> tab2
WHERE tab1.<ColumnName 1>=tab2.<ColumnName 1>
Example
DELETE FROM EMPLOYEE_HISTORY tab1 WHEREEXISTS(SELECT FIRSTNAME FROM EMPLOYEE tab2
WHERE tab1.ID=tab2.ID);
Hello! I am Narayanaswamy founder and admin of narayanatutorial.com. I have been working in the IT industry for more than 12 years. NarayanaTutorial is my web technologies blog. My specialties are Java / J2EE, Spring, Hibernate, Struts, Webservices, PHP, Oracle, MySQL, SQLServer, Web Hosting, Website Development, and IAM(ForgeRock) Specialist
I am a self-learner and passionate about training and writing. I am always trying my best to share my knowledge through my blog.
