SQL DELETE OPERATIONS
Table of Contents
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
DELETE FROM <TableName> WHERE <ColumnName 1> = <Condition>
DELETE FROM EMPLOYEE WHERE EMPLOYEE_ID=13;
DELETE all rows
DELETE FROM <TableName>
DELETE FROM EMPLOYEE
DELETE specific rows based on the data held by the other table
DELETE FROM <TableName 1> tab1 WHERE EXISTS(SELECT <ColumnName 1> FROM <TableName 2> tab2 WHERE tab1.<ColumnName 1>=tab2.<ColumnName 1>
DELETE FROM EMPLOYEE_HISTORY tab1 WHERE EXISTS(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 IT industry more than 7 years. NarayanaTutorial is my web technologies blog. My specialties are Java / J2EE, Spring, Hibernate, Struts, Webservices, PHP, Oracle, MySQL, SQLServer, Web Hosting and Website Development.
I am a self learner and passionate about training and writing. I am always trying my best to share my knowledge through my blog.