Postgresql remove duplicate rows based on specific columns-CodePudding

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    |

CodePudding user response：

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.

CodePudding user response：

You can use group by for get unique setup_id.

SELECT * FROM TABLE_NAME GROUP BY setup_id, menu

CodePudding user response：

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);

CodePudding user response：

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

CodePudding user response：

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 );

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

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;