Deleting duplicates on combination of two columns in oracle-CodePudding

I have a table for example : Port Table

S No	A Port	B port
1	80	100
2	90	110
3	100	80
4	94	106

I want to delete record no. 3 as it is having same combination as record no. 1 how to do this in oracle ?

CodePudding user response：

You can use a single MERGE statement and the ROW_NUMBER analytic function combined with GREATEST and LEAST to find and delete the duplicates:

MERGE INTO table_name dst
USING (
  SELECT ROWID rid,
         ROW_NUMBER() OVER (
           PARTITION BY LEAST(A_Port, B_Port), GREATEST(A_Port, B_Port)
           ORDER BY S_no
         ) AS rn
  FROM   table_name
) src
ON (dst.ROWID = src.rid AND src.rn > 1)
WHEN MATCHED THEN
  UPDATE SET A_port = NULL
  DELETE WHERE 1 = 1;

Which, for your sample data:

CREATE TABLE table_name (S_No, A_Port, B_port) AS
SELECT 1,  80, 100 FROM DUAL UNION ALL
SELECT 2,  90, 110 FROM DUAL UNION ALL
SELECT 3, 100,  80 FROM DUAL UNION ALL
SELECT 4,  94, 106 FROM DUAL;

Will delete the 3rd row.

db<>fiddle here

CodePudding user response：

Tried this in mysql, do some test/scenarios

SELECT P1.* FROM port_tbl AS P1 
LEFT JOIN port_tbl AS P2 ON P1.port1 = P2.port2 OR P1.port2 = P2.port1 
WHERE P1.id < P2.id OR ISNULL(P2.id) 
ORDER BY P1.id;