SQLite UPDATE value with row number possible/options?

Question

Hi i'm working on an SQLite viewer in android using java and shell commands (so commands have to be one line) i've written the layout and viewer and all everything is perfect. I set it up so when a value is long pressed an edit text shows where the user can input the new value and then when okay is pressed it should update the value.

I have the column name, old and new value, database name, table name etc however the issue is allowing them to update the value i've seen things like the where clause but the issue is if a column has the value multiple times (which could very well be the case) it won't know the correct row.

So bear in mind i'm new to sqlite been working with it less than a week. Is there a way i can update the column value with the columns name and row number?

What are my options to update the table.

I also don't really under stand this say i have a table like this (one column)

sample
--------
0
0
0
1
0
1
1
0

How would you update the third row to equal 1? If I did

sqlite3 DATABASEHERE "UPDATE TABLEHERE SET sample='1' WHERE sample='0'"

The where statement describes rows 1, 2, 3, 5, and 8 so there has to be a way to use row number?

Thanks for any help

Answer 1

SQLite gives you access to rowid. So you can write SELECT rowid, col FROM table1 and then use it to update the table :UPDATE TABLEHERE SET sample='1' WHERE rowid=3