CODEWITHSUNDEEP
sqloracle-databaseduplicates
Iftekhar
Iftekhar

Reputation: 365

how to create a table with only the duplicate rows and delete the duplicates from the main table?

I want to create a temporary table in Oracle where I can just keep the duplicate rows from the main table and then delete the duplicates from the main table. The main table here in this case is tmp_emp_area.

SQL> desc tmp_emp_Area
 Name                            Null?    Type
 ------------------------------- -------- ----
 SC_CD                                    VARCHAR2(2)
 DIST_CD                                  VARCHAR2(2)
 THA_CD                                   VARCHAR2(2)
 UN_CD                                    VARCHAR2(3)
 FP_ID                                    VARCHAR2(4)
 S_DT                                     DATE
 END_DT                                   DATE
 PERFORM                                  VARCHAR2(1)
 BS_CD                                    VARCHAR2(4)
 MKT_CD                                   VARCHAR2(3)

query :

select SC_CD,DIST_CD,THA_CD,UN_CD,FP_ID,count(fp_id)  
from tmp_emp_area 
group by SC_CD,DIST_CD,THA_CD,UN_CD,FP_ID 
having count(fp_id)>1

result : 

SC DI TH UN_ FP_I COUNT(FP_ID)
-- -- -- --- ---- ------------
14 61 02 022 5J85            2
14 61 02 098 5J85            3
14 64 02 004 5J85            2
14 64 02 002 5J85            2
14 60 19 060 5F77            2
14 60 13 077 5F77            2
14 61 06 006 5D51            2
14 61 07 013 5D51            2
14 61 07 083 5D51            2
14 61 06 010 5D51            2
14 61 01 015 5R44            2
14 61 08 027 5R44            2
14 61 01 057 5R44            2
14 61 01 067 5R44            2
14 61 05 001 5R44            2
14 61 05 003 5R44            2
14 61 02 009 5J85            2
14 60 13 078 5F77            2
14 61 06 007 5D51            2
14 61 01 021 5R44            2
14 61 01 029 5R44            2

Upvotes: 2

Views: 122

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

i want to create a temporary table in oracle where i can just keep the duplicate rows from the main table

You could use the analytic function ROW_NUMBER.

CREATE TABLE temp AS
SELECT SC_CD,
  DIST_CD,
  THA_CD,
  UN_CD,
  FP_ID
FROM
  (SELECT SC_CD,
    DIST_CD,
    THA_CD,
    UN_CD,
    FP_ID,
    row_number() over(partition BY SC_CD, DIST_CD, THA_CD, UN_CD, FP_ID order by FP_ID) rn
  FROM tmp_emp_area
  )
WHERE rn >1;

and then delete the duplicates from the main table

Using the same logic as above, you could delete either on the basis of the primary key, or ROWID.

DELETE
FROM tmp_emp_area
WHERE ROWID IN
  (SELECT rowid
  FROM
    (SELECT rowid,
      row_number() over(partition BY SC_CD, DIST_CD, THA_CD, UN_CD, FP_ID order by FP_ID) rn
    FROM tmp_emp_area
    )
  WHERE rn >1
  );

Upvotes: 1

Related Questions