How to remove similar rows from a oracle table(a complex one)?-CodePudding

I need to remove duplicate rows from a table i.e. the original and the duplicate one. I have a table as below where I need to remove all duplicates records for users, 'John' and 'James' users records in below case

**Input Table**
| Name  | email       | N  | country|operation| 
| ----  | -----  ---  |----| -------|--------
| John  |[email protected] | 90 | Canada | Delete | 
| John  |[email protected] | 90 | Canada | Insert | 
| John  |[email protected] | 50 | USA    | Insert | 
| Thomas|[email protected]  | 50 | USA    | Delete |
| David |[email protected]  | 60 | Mexico | Insert |
| James |[email protected] | 70 | Spain  | Delete |
| James |[email protected] | 70 | Spain  | Insert |
| James |[email protected] | 66 | UK     | Insert |
| Alex  |[email protected] | 65 | Denmark| Insert |

    

**Expected Output**
| name |  email       | N  | country| operation
| ----  | ------------| ---|--------| ---------|
| Thomas|[email protected]  | 50 | USA    | Delete
| John  |[email protected] | 50 | USA    | Insert 
| David |[email protected]  | 60 | Mexico | Insert
| Alex  |[email protected] | 65 | Denmark| Insert
| James |[email protected] | 66 | UK     | Insert

CodePudding user response：

delete from table
where name in (
  select name from table
  group by name
  having count(*) > 1)

CodePudding user response：

Withh your data as in the question:

Create table A_TBL  AS
    
            Select 'John'  "A_NAME", '[email protected]' "EMAIL", 90 "N", 'Canada' "COUNTRY", 'Delete' "OPERATION" From Dual Union All
            Select 'John'  "A_NAME", '[email protected]' "EMAIL", 90 "N", 'Canada' "COUNTRY", 'Delete' "OPERATION" From Dual Union All
            Select 'John'  "A_NAME", '[email protected]' "EMAIL", 50 "N", 'USA' "COUNTRY", 'Delete' "OPERATION" From Dual Union All
            Select 'Thomas'  "A_NAME", '[email protected]' "EMAIL", 50 "N", 'USA' "COUNTRY", 'Delete' "OPERATION" From Dual Union All
            Select 'David'  "A_NAME", '[email protected]' "EMAIL", 60 "N", 'Mexico' "COUNTRY", 'Delete' "OPERATION" From Dual Union All
            Select 'James'  "A_NAME", '[email protected]' "EMAIL", 70 "N", 'Spain' "COUNTRY", 'Delete' "OPERATION" From Dual Union All
            Select 'James'  "A_NAME", '[email protected]' "EMAIL", 70 "N", 'Spain' "COUNTRY", 'Delete' "OPERATION" From Dual Union All
            Select 'James'  "A_NAME", '[email protected]' "EMAIL", 66 "N", 'UK' "COUNTRY", 'Delete' "OPERATION" From Dual Union All
            Select 'Alex'  "A_NAME", '[email protected]' "EMAIL", 65 "N", 'Denmark' "COUNTRY", 'Delete' "OPERATION" From Dual;

here is the solution:

DELETE FROM A_TBL
WHERE A_NAME || '-' || EMAIL || '-' || To_Char(N) || '-' || COUNTRY IN
    (
        SELECT 
            A_NAME || '-' || EMAIL || '-' || To_Char(N) || '-' || COUNTRY "MY_KEY"
        FROM
            (
                Select
                    t.A_NAME, t.EMAIL, t.N, t.COUNTRY, t.OPERATION,
                    Sum(1) OVER(PARTITION BY t.A_NAME || '-' || t.EMAIL || '-' || To_Char(t.N) || '-' || t.COUNTRY
                                          ORDER BY t.A_NAME || '-' || t.EMAIL || '-' || To_Char(t.N) || '-' || t.COUNTRY
                                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "ORD",
                    COUNT(OPERATION) OVER(PARTITION BY t.A_NAME || '-' || t.EMAIL || '-' || To_Char(t.N) || '-' || t.COUNTRY
                                          ORDER BY t.A_NAME || '-' || t.EMAIL || '-' || To_Char(t.N) || '-' || t.COUNTRY) "CNT"
                From
                    A_TBL t
            )
        WHERE CNT > 1 AND ORD = 1
    )

There is a column MY_KEY created to be like an index. Besides there are two more columns - CNT with numbers of rows for that new key and ORD with ordering the duplicate keys. The rest is ease just delete from table having concatinated table values in the list of keys from the subset where CNT > 1 and ORD = 1. Hope this could help you.
The result is:

/*
4 rows deleted.
SELECT * FROM A_TBL;
A_NAME EMAIL                   N COUNTRY OPERATION
------ -------------- ---------- ------- ---------
John   [email protected]           50 USA     Delete    
Thomas [email protected]         50 USA     Delete    
David  [email protected]          60 Mexico  Delete    
James  [email protected]          66 UK      Delete    
Alex   [email protected]           65 Denmark Delete
*/

The OPERATION column created with all the same values, sorry. But that doesn't change anything.. Regards...