SQL Delete
 

Oracle SQL 

SQL Introduction    SQL Introduction 

SQL Insert    SQL Insert

SQL Update    SQL Update

SQL Delete    SQL Delete

SQL Joins   SQL Joins 

SQL Subqueries    SQL Subqueries

SQL Views    SQL Views

Oracle PL/SQL

PL/SQL Introduction    PL/SQL Introduction

PL/SQL Cursors    PL/SQL Cursors

PL/SQL Triggers    PL/SQL Triggers

PL/SQL Interview Questions    PL/SQL Questions

SQL – Delete

DELETE:

How to delete all records from a table?

Delete from dept;

How to delete specific records from a table?

Delete from emp where empno=20;

How to delete duplicate records from the table?

Suppose we have a table t1(id integer, name varchar(10))

select * from t1;

id       name

---------------

1        aaa

2        bbb

3        bbb

4        ccc

5        ccc

6        ddd

delete from t1

             where id not in ( select min(id)

                                  from t1

                                 group by name )

Few Examples:

BOTH THE BELOW EXAMPLES OF UPDATE AND DELETE USE CORRELATED SUBQUERIES:

We need to update sal of all the employees in the emp table to the maximum salary in the corresponding dept.

UPDATE emp e1

SET sal = (SELECT MAX(sal)

FROM emp e2

WHERE e1.deptno = e2.deptno);

We need To delete the records of all the employees in the emp table whose sal is below the average sal in the department

DELETE FROM emp e

WHERE sal < (SELECT AVG(sal) FROM emp

WHERE deptno = e.deptno);