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;
delete from t1
where id not in ( select min(id)
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);