Delete duplicate rows based on two values in sql [duplicate]

Question

I'm new to sql and I can't work out how to delete duplicate rows, I have a table like this called 'till_total':

till_id	total
1	80
1	80
1	60
2	30
2	30
2	50

I want to only delete full duplicate rows so the table ends up like this

till_id	total
1	80
1	60
2	30
2	50

I wrote this code to try and do it

SELECT till_id, total, COUNT(*) AS CNT
FROM till_total
GROUP BY till_id, total
HAVING COUNT(*) > 1
ORDER BY till_id;

But that seems to delete all rows where the till_id is repeated. Could anyone help me with this?

Ironic that this is a duplicate question :)

William Robertson
– William Robertson

2022-08-20 10:19:23 +00:00
Commented Aug 20, 2022 at 10:19 — William Robertson
– William Robertson, Commented Aug 20, 2022 at 10:19

Littlefoot · Accepted Answer · 2022-08-19 10:58:13Z

1

Good, old ROWID approach:

Before:

SQL> select * from till_total;

   TILL_ID      TOTAL
---------- ----------
         1         80
         1         80
         1         60
         2         30
         2         30
         2         50

6 rows selected.

Delete duplicates:

SQL> delete from till_total a
  2  where a.rowid > (select min(b.rowid)
  3                   from till_total b
  4                   where b.till_id = a.till_id
  5                     and b.total = a.total
  6                  );

2 rows deleted.

After:

SQL> select * from till_total;

   TILL_ID      TOTAL
---------- ----------
         1         80
         1         60
         2         30
         2         50

SQL>

answered Aug 19, 2022 at 10:58

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

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

Comments

MiNi · Accepted Answer · 2022-08-19 10:52:04Z

0

WITH till_total AS (
  SELECT till_id
     row_number() OVER(PARTITION BY till_id ORDER BY desc) AS row
  FROM TABLE
)
DELETE till_total WHERE row > 1

This might work for you, deleting rows that are more than 1 duplicate, not less than 1.

answered Aug 19, 2022 at 10:52

MiNi

113 bronze badges

1 Comment

Littlefoot Over a year ago

Nope, that won't work. Did you try it?

Collectives™ on Stack Overflow

Delete duplicate rows based on two values in sql [duplicate]

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related