SQL 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 on SQL Delete:

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