How to Find and Delete Duplicate Rows with Oracle SQL
SQL> select * from test;
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES President 20080 40000
AD_VP Administration Vice President 15000 30000
AD_ASST Administration Assistant 3000 6000
FI_MGR Finance Manager 8200 16000
FI_ACCOUNT Accountant 4200 9000
AC_MGR Accounting Manager 8200 16000
AC_ACCOUNT Public Accountant 4200 9000
SA_MAN Sales Manager 10000 20080
SA_REP Sales Representative 6000 12008
PU_MAN Purchasing Manager 8000 15000
PU_CLERK Purchasing Clerk 2500 5500
ST_MAN Stock Manager 5500 8500
ST_CLERK Stock Clerk 2008 5000
SH_CLERK Shipping Clerk 2500 5500
IT_PROG Programmer 4000 10000
MK_MAN Marketing Manager 9000 15000
MK_REP Marketing Representative 4000 9000
HR_REP Human Resources Representative 4000 9000
PR_REP Public Relations Representative 4500 10500
19 rows selected.
SQL> select min_salary ,count(*) from test group by min_salary having count(*)>1;
MIN_SALARY COUNT(*)
---------- ----------
4200 2
8200 2
2500 2
4000 3
SQL> select * from test where MIN_SALARY in (select min_salary from test group by min_salary having count(*)>1);
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
FI_ACCOUNT Accountant 4200 9000
AC_ACCOUNT Public Accountant 4200 9000
FI_MGR Finance Manager 8200 16000
AC_MGR Accounting Manager 8200 16000
PU_CLERK Purchasing Clerk 2500 5500
SH_CLERK Shipping Clerk 2500 5500
IT_PROG Programmer 4000 10000
MK_REP Marketing Representative 4000 9000
HR_REP Human Resources Representative 4000 9000
9 rows selected.
SQL>
SQL> select * from test where MIN_SALARY in (select min_salary from test group by min_salary having count(*)>1);
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
FI_ACCOUNT Accountant 4200 9000
AC_ACCOUNT Public Accountant 4200 9000
FI_MGR Finance Manager 8200 16000
AC_MGR Accounting Manager 8200 16000
PU_CLERK Purchasing Clerk 2500 5500
SH_CLERK Shipping Clerk 2500 5500
IT_PROG Programmer 4000 10000
MK_REP Marketing Representative 4000 9000
HR_REP Human Resources Representative 4000 9000
9 rows selected.
SQL> delete from test where MIN_SALARY in (select min_salary from test group by min_salary having count(*)>1);
9 rows deleted.
SQL> select * from test;
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES President 20080 40000
AD_VP Administration Vice President 15000 30000
AD_ASST Administration Assistant 3000 6000
SA_MAN Sales Manager 10000 20080
SA_REP Sales Representative 6000 12008
PU_MAN Purchasing Manager 8000 15000
ST_MAN Stock Manager 5500 8500
ST_CLERK Stock Clerk 2008 5000
MK_MAN Marketing Manager 9000 15000
PR_REP Public Relations Representative 4500 10500
10 rows selected.
- Use "commit" after delete statement make as permanent delete.
No comments:
Post a Comment