SQL Tutorials

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