Narayana Tutorial

Online Java Tutorial Blog

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);

 

Narayanaswamy

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.

Leave a Reply

Narayana Tutorial © 2018 Frontier Theme
Show Button
Hide Button