[SOLVED] How to delete a row knowing its row index in a nested SQL statement where the table doesn't have a row index column?

How to delete a row knowing its row index in a nested SQL statement where the table doesn't have a row index column?

My app has a function that deletes a specific row in a SQLite table based on the row index or position, but the table doesn't have a column that stores a row index for easy access. My app retrieves the row index by calling mytableLayout.indexOfChild(myview).toString() from the MainActivity.kt file.

How do I search for the row based on row index and then delete the row all in one SQL nested statement?

fun delete(rowindex: Int) {
// get the writable database
val db = databaseHelper.writableDatabase

db.execSQL("DELETE FROM myTable WHERE EXISTS (SELECT * FROM myTable LIMIT 1 OFFSET $rowindex)")

// close the database connection
db.close()
}

Even though my app compiles and runs, this SQL statement deletes all the rows, not just the offset row.

My table does have a row index column. It is a ROW ID column that AUTO INCREMENTS every time a row is added. However, this column is not displayed. Therefore, I don't have access to this information for the row to be deleted. At one time, I did include this column in the layout and pass it to function delete. That worked, but I don't want to display this column. So I made the column invisible, but the layout still treated that column as being shown with nothing showing or empty space.

Solution

All the answers posted make good points about the use case of the SQL statements in this situation. However, even though I don't have RowId column in my table as mentioned by others, I do have Auto Increment column that gets added as rows are added. So, I ended up using it as RowId and used "keyboard corporation" SQL statement with minor change.

DELETE FROM myTable WHERE ROWID = (SELECT ROWID FROM myTable LIMIT 1 OFFSET ($rowIndex-1))

It works as it should.