Delete Duplicate data by using PRIMARY KEY

 
My Tech Channel link:- https://www.youtube.com/oracleshooter
 
 
CREATE TABLE XX_DELIVERY_DATA_TL
( ID                              NUMBER PRIMARY KEY,                     
  ORDER_NUMBER          VARCHAR2(500 BYTE),
  TRX_TYPE            VARCHAR2(500 BYTE) ,
  VENDOR_CODE  VARCHAR2(100 BYTE),
  PROCESS_FLAG        VARCHAR2(5 BYTE)
)
 
 
select * from XX_DELIVERY_DATA_TL
 
create sequence XX_DEL_SEQ

 
INSERT INTO XX_DELIVERY_DATA_TL
select XX_DEL_SEQ.NEXTVAL, '98981210001', 'Delivery Feed', 'Cun Mun', 'N' FROM DUAL
 
INSERT INTO XX_DELIVERY_DATA_TL
select XX_DEL_SEQ.NEXTVAL, '98981210002', 'Delivery Feed', 'Vishal maga Mart', 'N'  FROM DUAL
 
INSERT INTO XX_DELIVERY_DATA_TL
select XX_DEL_SEQ.NEXTVAL, '98981210003', 'Delivery Feed', 'India Pvt Ltd', 'N'  FROM DUAL
 
--=====================
 
select * from XX_DELIVERY_DATA_TL A
order by 2
 
 
--====================================================
 
 SELECT MAX(ID) FROM XX_DELIVERY_DATA_TL where VENDOR_CODE = 'Cun Mun'
 GROUP BY ORDER_NUMBER, TRX_TYPE, VENDOR_CODE,PROCESS_FLAG
 
DELETE XX_DELIVERY_DATA_TL A
--
 
select * from  XX_DELIVERY_DATA_TL A
WHERE 1=1
--AND A.VENDOR_CODE = 'Cun Mun'
AND ID NOT IN ( SELECT MIN(ID) FROM XX_DELIVERY_DATA_TL Z WHERE A.VENDOR_CODE = Z.VENDOR_CODE  GROUP BY ORDER_NUMBER, TRX_TYPE, VENDOR_CODE,PROCESS_FLAG );                             
 

Comments

Popular posts from this blog

E-Text Report In Fusion | Types of E-Text reports

Supplier API's

How to pass default Parameter in cursor