2

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'";
Improve this question
4
  • That question does not apply to SQLite. Commented Aug 5, 2013 at 12:28
  • 1
    take a look at this stackoverflow.com/questions/8190541/… Commented Aug 5, 2013 at 12:39
  • @xperator have you tried the above link? Commented Aug 5, 2013 at 12:48
  • @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 Commented Aug 5, 2013 at 13:21

3 Answers 3

Reset to default
0

You could potentially use RowNumber() over()

delete from tbl_data where Rownumber() Over(partition by rc_id, phonenumber Order by rc_id) > 1
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

This does not work in SQLite.
ahh missed that tag, sorry
-1

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

Improve this answer

3 Comments

Refer support.microsoft.com/kb/139444 link
This does not work in SQLite.
I didn't checked for the tag. Anyways.... I thought that the answer would be appreciated...
-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')";
Improve this answer

5 Comments

This does not work in SQLite.
sorry, missed the sqlite tag, too... edited answer...
I get syntax error on LIMIT cause.
LIMIT on DELETE is an optional extension that is not enabled by default. See EhsanUllah's link for a working solution.
The linked solution (see EhsanUllah) does not work, because here are no unique identifiers available.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.