Delete duplicate rows in relationship table [duplicate]

Question

I have a relationship table in a PostgreSQL database. I want to delete duplicate rows.

My table looks like this:

idUser idFunction 1 1 2 1 3 1 3 1 4 1 4 1 4 2

And I want this:

idUser idFunction 1 1 2 1 3 1 4 1 4 2

I have already tried this :

DELETE TOP (SELECT COUNT(*) -1 FROM user_function WHERE idUser IN (SELECT idUser FROM user_function GROUP BY idUser, idFunction HAVING COUNT(*) > 1) AND idFunction IN (SELECT idFunction FROM user_function GROUP BY idUser, idFunction HAVING COUNT(*) > 1)) FROM user_function WHERE idUser IN (SELECT idUser FROM user_function GROUP BY idUser, idFunction HAVING COUNT(*) > 1) AND idFunction IN (SELECT idFunction FROM user_function GROUP BY idUser, idFunction HAVING COUNT(*) > 1)

But PostgreSQL tells me that there is an error at 'TOP'.

How can I delete duplicate rows in a relationship table in PostgreSQL?

Gordon Linoff · Accepted Answer · 2019-12-04 15:30:50Z

I might suggest just recreating the table:

create table temp_user_function as select distinct idUser, idFunction from user_function; truncate table user_function; -- backup first!!! insert into user_function (idUser, idFunction) select idUser, idFunction from temp_user_function;

You should declare the pair to be unique when you have fixed the data.

Collectives™ on Stack Overflow

Delete duplicate rows in relationship table [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related