Friday, January 19, 2007
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 )
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);