SQLite delete one single row

Question

I have a SQLite table defined this way:

CREATE TABLE Points(value INTEGER, player INTEGER, match INTEGER)

In the execution, I may have several identical columns, and I want a call which only deletes one, not all of them nor keeping just one. Is there any SQL call to do that?

An example to explain myself clearer:

value player match
1     2      3
1     3      3
1     2      3
2     2      3
1     2      3
1     2      3
1     3      3

db.delete("Points", "value = 1, player = 2, match = 3", null); //pseudo-code for the deletion
db.delete("Points", "value = 1, player = 3, match = 3", null);

value player match
1     2      3
2     2      3
1     2      3
1     2      3
1     3      3

I think db.delete("Points", "value = 1, player = 3, match = 3", null); will delete ALL columns which match the where clauses, am I right?

Didn't know about this statement. I will try and see if it works. Thank you very much! — user1897262, Apr 27 '13 at 10:05

score 1 · Accepted Answer · answered Apr 27 '13 at 10:11

1

The delete statement you wrote will indeed delete all matching rows. Try to use the built-in column ROWID, read the first line, save the rowid, and then delete where ROWID= the value you selected.

answered Apr 27 '13 at 10:11

yoah

7,180
2
30
30

I had thought about this but I expected there would be an "easier" way or an SQL statement to do this. Thank you for the answer. – user1897262 Apr 27 '13 at 10:17
After fighting with the LIMIT 1 clause (not enabled in SQLite) I am trying this way. – user1897262 Apr 27 '13 at 10:46

score 1 · Answer 2 · answered Mar 12 '15 at 19:24

1

The usual way to do this is to assign every table a unique identifier for each row. Like this:

CREATE TABLE Points(Id INTEGER PRIMARY KEY, value INTEGER, player INTEGER, match INTEGER);

Then you can use a subquery to find the one row you want to delete:

DELETE FROM Points WHERE Id =(SELECT MIN(Id) FROM Points WHERE value=1 AND player=2 and match=3);

This example deletes the oldest record entered first (the oldest has the lowest unique key Id)

answered Mar 12 '15 at 19:24

Jay

13,803
4
42
69

It is a waste to create `id integer primary key` in sqlite because it's done for you although the column is called `rowid`. – lmat - Reinstate Monica Mar 18 '22 at 21:06
1

Thanks for pointing that out. It has some limitations that might make it a bad choice: "If the rowid is not aliased by INTEGER PRIMARY KEY then it is not persistent and might change. In particular the VACUUM command will change rowids for tables that do not declare an INTEGER PRIMARY KEY. Therefore, applications should not normally access the rowid directly, but instead use an INTEGER PRIMARY KEY. " – Jay Mar 19 '22 at 04:33

score 0 · Answer 3 · answered Apr 27 '13 at 10:03

0

Try this

String _val=""+1;
String _player=""+3;
String _match=""+3;

db.delete(TABLE_NAME,"value=? AND player=? AND match=?",new String[]  {_val,_player,_match});

answered Apr 27 '13 at 10:03

android_dev

1,477
12
18

Won't this delete all the columns with value=1, player =3 and match=3? – user1897262 Apr 27 '13 at 10:07

SQLite delete one single row

3 Answers3