Delete a record's duplicates but leave 1 (without Identifier)

Question

Let's say we have this data:

rc_id      phonenumber
1               0
2               0
5               1
5               3
5               3
5               3

and I want to delete records matching rc_id of 5 and phonenumber of 3 while keeping at least 1 of them

The final result should be:

rc_id      phonenumber
1               0
2               0
5               1
5               3

Note: I don't want to use an unique identifier

I'm using this statement at the moment, but I noticed this deletes all other duplicates as well.

cmd.CommandText = "DELETE FROM tbl_data WHERE rc_id='5' AND phonenumber='3'";

take a look at this http://stackoverflow.com/questions/8190541/deleting-duplicate-rows-from-sqlite-database — Ehsan, Aug 05 '13 at 12:39
@EhsanUllah I just read that and I kinda understand how it works. But the statements used in there doesn't have a `WHERE` clause. It used a `GROUP BY` which I don't know how it works. I think the OP in there wants to delete any duplicates in the entire DB — xperator, Aug 05 '13 at 13:21

score 0 · Answer 1 · answered Aug 05 '13 at 12:24

0

You could potentially use RowNumber() over()

delete from tbl_data where  Rownumber() Over(partition by rc_id, phonenumber Order by rc_id) > 1

answered Aug 05 '13 at 12:24

RoughPlace

1,111
1
13
23

2

This does not work in SQLite. – CL. Aug 05 '13 at 12:27
ahh missed that tag, sorry – RoughPlace Aug 05 '13 at 12:28

score -1 · Answer 2 · answered Aug 05 '13 at 12:23

-1

set rowcount 1 delete from tbl_data where rc_id='5' AND phonenumber='3'

answered Aug 05 '13 at 12:23

Nisarg Shah

354
1
14

Refer http://support.microsoft.com/kb/139444 link – Nisarg Shah Aug 05 '13 at 12:25
3

This does not work in SQLite. – CL. Aug 05 '13 at 12:27
I didn't checked for the tag. Anyways.... I thought that the answer would be appreciated... – Nisarg Shah Aug 05 '13 at 12:29

Rob · Answer 3 · 2013-08-05T13:05:42.943

-1

What about this:

cmd.CommandText = "DELETE FROM tbl_data WHERE rc_id='5' AND phonenumber='3'
                   LIMIT (SELECT COUNT(*)-1 FROM tbl_data WHERE rc_id='5' AND phonenumber='3')";

edited Aug 05 '13 at 13:05

answered Aug 05 '13 at 12:35

Rob

919
7
16

1

This does not work in SQLite. – CL. Aug 05 '13 at 12:40
sorry, missed the sqlite tag, too... edited answer... – Rob Aug 05 '13 at 13:06
I get syntax error on `LIMIT` cause. – xperator Aug 05 '13 at 13:15
`LIMIT` on `DELETE` is an optional extension that is not enabled by default. See EhsanUllah's link for a working solution. – CL. Aug 05 '13 at 13:20
The linked solution (see EhsanUllah) does not work, because here are no unique identifiers available. – Rob Aug 05 '13 at 13:39

Delete a record's duplicates but leave 1 (without Identifier)

3 Answers3