'Deleting duplicates on combination of two columns in oracle

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 ?

sqloracle


Solution 1:[1]

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

Solution 2:[2]

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;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 MT0
Solution 2 ceejeey

Related Questions