Delete Duplicate data by using ROWID

 

CREATE TABLE APPS.XX_DELIVERY_DATA_TL

(

  ORDER_NUMBER          VARCHAR2(500 BYTE),

  TRX_TYPE            VARCHAR2(500 BYTE) Default 'Delivery Feed',

  VENDOR_CODE  VARCHAR2(100 BYTE),

  PROCESS_FLAG        VARCHAR2(5 BYTE)

)

 

 

select * from XX_DELIVERY_DATA_TL

 

INSERT INTO XX_DELIVERY_DATA_TL

select  '98981210001', 'Delivery Feed', 'Cun Mun', 'N'  FROM DUAL

 

INSERT INTO XX_DELIVERY_DATA_TL

select  '98981210002', 'Delivery Feed', 'Vishal maga Mart', 'N'  FROM DUAL

 

INSERT INTO XX_DELIVERY_DATA_TL

select  '98981210003', 'Delivery Feed', 'India Pvt Ltd', 'N'  FROM DUAL

 

 

--====================================================

 

DELETE FROM XX_DELIVERY_DATA_TL

 

select DISTINCT ORDER_NUMBER, TRX_TYPE, VENDOR_CODE, PROCESS_FLAG from XX_DELIVERY_DATA_TL A

 

select ORDER_NUMBER, TRX_TYPE, VENDOR_CODE, PROCESS_FLAG ,count(*) 

from XX_DELIVERY_DATA_TL

group by ORDER_NUMBER, TRX_TYPE, VENDOR_CODE, PROCESS_FLAG

 

 

select ROWID, A.* from XX_DELIVERY_DATA_TL A order by 2

 

 

select * from XX_DELIVERY_DATA_TL

 

delete from XX_DELIVERY_DATA_TL

WHERE rowid NOT IN ( SELECT MIN(rowid) FROM XX_DELIVERY_DATA_TL GROUP BY ORDER_NUMBER, TRX_TYPE, VENDOR_CODE,PROCESS_FLAG );