Removing Duplicate Rows from Table in Oracle

Removing duplicate rows from table in Oracle

Use the rowid pseudocolumn.

DELETE FROM your_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM your_table
GROUP BY column1, column2, column3);

Where column1, column2, and column3 make up the identifying key for each record. You might list all your columns.

To delete duplicate rows in Oracle SQL

Remove the second column from the inner SELECT, something like this:

DELETE
  FROM emp
  WHERE name IN( SELECT name FROM emp GROPU BY name HAVING COUNT(name) > 1)

How to delete duplicate rows in Oracle

Did you try it like this ?

DELETE FROM names a
WHERE ROWID > (SELECT MIN(ROWID) FROM names b
WHERE b.doc_name=a.doc_name
AND b.ref_no=a.ref_no
)

try this also

SELECT *
  FROM doc_unique
 WHERE (DIV_CD, DOC_NAME, B_DT, FT_NM, UNQ_CD, DESG_CD,
 SPEC_CD) IN (SELECT DIV_CD, DOC_NAME, B_DT, FT_NM, UNQ_CD, DESG_CD,
 SPEC_CD
                             FROM doc_unique
                            GROUP BY DIV_CD, DOC_NAME, B_DT, FT_NM, UNQ_CD, DESG_CD,
 SPEC_CD HAVING COUNT(*) > 1)

Remove duplicate row from view oracle

Following the results returned, the easiest way would be to use DISTINCT in your SELECT:

 CREATE OR REPLACE FORCE VIEW "GLSID"."VW_GLSID_DOCUMENTS" ("ID", "PARENT_ID", "DOCUMENT_TYPE_ID", "AMOUNT") AS 
  SELECT DISTINCT container.id,
   container.container_id AS parent_id,
   document.document_type_id,
   container.number_of_documents AS amount
   FROM container
   JOIN document ON document.container_id = container.id ;

If you discover errors in your document count, you might try this instead:

 CREATE OR REPLACE FORCE VIEW "GLSID"."VW_GLSID_DOCUMENTS" ("ID", "PARENT_ID", "DOCUMENT_TYPE_ID", "AMOUNT") AS 
  SELECT container.id,
         container.container_id AS parent_id,
         document.document_type_id,
         SUM(container.number_of_documents) AS amount
   FROM container
   JOIN document ON document.container_id = container.id 
   GROUP BY container.id,
            container.container_id,
            document.document_type_id

How to delete duplicate records from a table in oracle

delete from table_name a
where 
a.rowid > any (select b.rowid from table_name b where a.col1 = b.col1 and a.col2 = b.col2);