0

I have a table.(table name: test_table)

+------+
| Col1 |
+------+
| a1   |
| b1   |
| b1   |
| c1   |
| c1   |
| c1   |
+------+

I wanted to delete duplicate rows except one row in duplicates, like this

+------+
| Col1 |
+------+
| a1   |
| b1   |
| c1   |
+------+

So, I thought

  1. make row number
  2. delete duplicates by row number

and failed to make row number with this query

ALTER TABLE test_table ADD COLUMN row_num INTEGER;
UPDATE test_table SET row_num = subquery.row_num
                  FROM (SELECT ROW_NUMBER() OVER () AS row_num
                        FROM test_table) AS subquery;

the result is below

+------+---------+
| Col1 | row_num |
+------+---------+
| a1   |       1 |
| b1   |       1 |
| b1   |       1 |
| c1   |       1 |
| c1   |       1 |
| c1   |       1 |
+------+---------+

what part need to change for getting like this?

+------+---------+
| Col1 | row_num |
+------+---------+
| a1   |       1 |
| b1   |       2 |
| b1   |       3 |
| c1   |       4 |
| c1   |       5 |
| c1   |       6 |
+------+---------+
beginner_s
  • 1
  • What you are trying to do is fairly difficult/inconvenient without there being a primary key column...which begs the question of, why doesn't your table have a primary key column? – Tim Biegeleisen Nov 09 '20 at 06:34
  • 1
    https://stackoverflow.com/a/26773018/330315 –  Nov 09 '20 at 07:00

0 Answers0