correct syntax to copy from one table row to another (sqlite android)

Question

Hello, please, is this the correct syntax for copying a particular row from one table to another?

db.execSQL("insert into Route select * from " + DATABASE_TABLE + " WHERE _id = rowId");

because for some reason, when i call the method that executes this statement, all the items from the first table are copied into the second table and not the specific row i want. thanks.

this is the method call for copy:

public void copyData(long rowId){
db.execSQL("insert into Route select * from " + DATABASE_TABLE + " WHERE _id = rowId"); 
}

if not, could you please show me how?..

Answer 1

I see a couple of problems:

First, you're not passing the rowId argument to your copyData() method in as part of the query, you're using the string literal rowID.

Second, ROWID has special meaning in SQLite. It's a pseudo-column that uniquely identifies each row in a table and is usually the same value that gets assigned to an INTEGER PRIMARY KEY column, which I'm guessing is how you have _id declared. The result is that when WHERE _id = ROWID is evaluated, the two values always match and therefore all rows are selected.