2

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.

volume one
  • 6,800
  • 13
  • 67
  • 146
  • `SELECT DISTINCT currencyid, bankid FROM yourtable;` ? – JNevill Feb 24 '22 at 19:14
  • @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 Feb 24 '22 at 19:14
  • 1
    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 Feb 24 '22 at 19:57

1 Answers1

2

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;
TCFDS
  • 584
  • 4
  • 16