How to delete duplicate rows and keep just one row with multiple columns

Question

I have duplicate data in a table called bank_currency that looks like this:

currencyid | bankid
--------------------
8             1
8             1
8             1
16            2
16            2
16            2
14            3
14            3
14            3

I have no idea why the data has been duplicated in triplicate, but I need to get rid of all the duplicates and keep only one of each row. So I end up like this:

currencyid | bankid
--------------------
8             1
16            2
14            3

I cannot ORDER BY the bankid or currencyid to tell postgresql which row to keep, because they are duplicate. Perhaps an order by ROW_NUMBER (if thats possible) and just keep the lowest ROW_NUMBER? Any suggestions greatly appreciated.

@JNevill Yes that will return me one row, but I want to delete the other rows. I want them removed out of the table so I need a DELETE statement — volume one
– volume one, Commented Feb 24, 2022 at 19:14
The real work is identifying which records you want to keep. So take this result set from DISTINCT write it into a temp table or what-have-you, delete all records from your existing table and then blow the records from your temp table into your original table. I think handling this with a single DELETE statement is going to introduce a level of complexity and risk that I, personally, wouldn't be comfortable with in my database. — JNevill
– JNevill, Commented Feb 24, 2022 at 19:57

TCFDS · Accepted Answer · 2022-02-24 19:32:09Z

4

If your table doesn't have id column, the best option could be using temporaray table:

CREATE TABLE bank_currency_temp AS
SELECT DISTINCT bankid, currencyid
FROM bank_currency;

After that remove original table

DROP TABLE bank_currency

Then rename temp table

ALTER TABLE bank_currency_temp
RENAME TO bank_currency;

answered Feb 24, 2022 at 19:32

TCFDS

6226 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Beefster Over a year ago

would be better if you included a way to do this in-place so that you don't have to do all sorts of re-setup for the table

Collectives™ on Stack Overflow

How to delete duplicate rows and keep just one row with multiple columns

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related