Remove duplicate rows based on specific columns

Question

I have a table that contains these columns:

ID (varchar)
SETUP_ID (varchar)
MENU (varchar)
LABEL (varchar)

The thing I want to achieve is to remove all duplicates from the table based on two columns (SETUP_ID, MENU).

Table I have:

id  |  setup_id  |  menu  |  label  |
-------------------------------------
1   |    10      |  main  |  txt    |
2   |    10      |  main  |  txt    |
3   |    11      | second |  txt    |
4   |    11      | second |  txt    |
5   |    12      | third  |  txt    |

Table I want:

id  |  setup_id  |  menu  |  label  |
-------------------------------------
1   |    10      |  main  |  txt    |
3   |    11      | second |  txt    |
5   |    12      | third  |  txt    |

Answer 1

You can achieve this with a common table expression (cte)

with cte as ( 
           select id, setup_id, menu, 
                  row_number () over (partition by setup_id, menu, label) rownum
           from atable )
delete from atable a
where id in (select id from cte where rownum >= 2) 

This will give you your desired output.

Common Table Expression docs

Answer 2

Assuming a table named tbl where both setup_id and menu are defined NOT NULL and id is the PRIMARY KEY.
EXISTS will do nicely:

DELETE FROM tbl t0
WHERE  EXISTS (
   SELECT FROM tbl t1
   WHERE  t1.setup_id = t0.setup_id
   AND    t1.menu = t0.menu
   AND    t1.id < t0.id
   );

This deletes every row where a dupe with lower id is found, effectively only keeping the row with the smallest id from each set of dupes. An index on (setup_id, menu) or even (setup_id, menu, id) will help performance with big tables a lot.

If there is no PK and no reliable UNIQUE (combination of) column(s), you can fall back to using the ctid. If NULL values can be involved, you need to specify how to deal with those.
Consider:

• Delete duplicate rows from small table
• How to delete duplicate rows without unique identifier
• How do I (or can I) SELECT DISTINCT on multiple columns?

After cleaning up duplicates, add a UNIQUE constraint to prevent new dupes:

ALTER TABLE tbl ADD CONSTRAINT tbl_setup_id_menu_uni UNIQUE (setup_id, menu);

If you had an index on (setup_id, menu), drop that now. It's superseded by the UNIQUE constraint.

Answer 3

I have found a solution that fits me the best. Here it is if anyone needs it:

DELETE FROM table_name
WHERE id IN
  (SELECT id
   FROM
       (SELECT id,
               ROW_NUMBER() OVER( PARTITION BY setup_id,
     menu
    ORDER BY  id ) AS row_num
        FROM table_name ) t
   WHERE t.row_num > 1 );

Answer 4

link: https://www.postgresql.org/docs/current/queries-union.html
https://www.postgresql.org/docs/current/sql-select.html#SQL-DISTINCT
let's sat table name is a

select distinct on (setup_id,menu ) a.* from a;

Key point: The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). The ORDER BY clause will normally contain additional expression(s) that determine the desired precedence of rows within each DISTINCT ON group.

Which means you can only order by setup_id,menu in this distinct on query scope.

Want the opposite:
EXCEPT returns all rows that are in the result of query1 but not in the result of query2. (This is sometimes called the difference between two queries.) Again, duplicates are eliminated unless EXCEPT ALL is used.

SELECT * FROM a
EXCEPT 
select distinct on (setup_id,menu ) a.* from a;

Answer 5

For mysql the similar question is already answered here Find and remove duplicate rows by two columns

Try if any of the approach helps in this matter.

I like the below one for MySql:

ALTER IGNORE TABLE your_table ADD UNIQUE (SETUP_ID, MENU);

Answer 6

You can try something along these lines to delete all but the first row in case of duplicates (please note that this is not tested in any way!):

DELETE FROM your_table WHERE id IN (
    SELECT unnest(duplicate_ids[2:]) FROM (
        SELECT array_agg(id) AS duplicate_ids FROM your_table
            GROUP BY SETUP_ID, MENU
            HAVING COUNT(*) > 1
        )
    )
)

The above collects the ids of the duplicate rows (COUNT(*) > 1) in an array (array_agg), then takes all but the first element in that array ([2:]) and "explodes" the id values into rows (unnest). The outer query just deletes every id that ends up in that result.

Answer 7

DELETE t1 
FROM table_name t1
    join table_name t2 on
    (t2.setup_id = t1.setup_id or t2.menu = t1.menu) and t2.id < t1.id

Answer 8

There are many ways to find and delete all duplicate row(s) based on conditions. But I like inner join method, which works very fast even in a large amount of Data. Please check follows :

DELETE T1 FROM <TableName> T1
INNER JOIN <TableName> T2 
WHERE
    T1.id > T2.id AND 
    T1.<ColumnName1> = T2.<ColumnName1> AND T1.<ColumnName2> = T2.<ColumnName2>;
  

In your case you can write as follows :

DELETE T1 FROM <TableName> T1
    INNER JOIN <TableName> T2 
    WHERE
        T1.id > T2.id AND 
        T1.setup_id = T2. setup_id;

Let me know if you face any issue or need more help.