CODEWITHSUNDEEP
oracle-databaseoracle12c
Random guy
Random guy

Reputation: 923

Deletting duplicate data on oracle using sql failed

I have a table abc as:

acc   subgroup
720V  A
720V  A
720V  A
720V  A
111   C
222   D
333   E

My expected output is:

    acc   subgroup
    720V  A
    111   C
    222   D
    333   E

Since 720V A is duplicate i want to delete all three duplicate data and only want one data in my table. So,i tried 

DELETE FROM (
select t.*,rownum rn from abc  t where acc='720V') where rn>1;

So,I get error as:

ORA-01732: data manipulation operation not legal on this view

How i can get my expected output?

Upvotes: 2

Views: 15

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522571

Your table seems to be lacking a primary key column, which is a big problem here. Assuming there actually is a primary key column PK, we can try using ROW_NUMBER to identify any "duplictes":

DELETE
FROM abc t1
WHERE pk IN (SELECT pk
             FROM (
                 SELECT t.pk, ROW_NUMBER() OVER (PARTITION BY acc, subgroup ORDER BY pk) rn
                 FROM abc t) x
             WHERE rn > 1
);

Note that if you can live with keeping your original data, then the most expedient thing to do might be to create a distinct view:

CREATE VIEW abc_view AS
SELECT DISTINCT acc, subgroup
FROM abc;

Upvotes: 1

Related Questions