How to update and select a row in one query in SqLite?

Question

I want to update and select one row in one query in SqLite. In MySql, my query will look like this:

SET @update_id := -1; UPDATE data SET `Status` = 'running', Id = (SELECT @update_id := Id) WHERE `Status` = 'scheduled' LIMIT 1; SELECT * FROM data WHERE id=@update _id;" 

The above query will set the status to "running" and the value of the @update_id variable in the Id of the changed row for the first row that has the status of "scheduled", and uses the @update_id variable to retrieve the full changed row.

The important point is that I need to select the row that was modified by the UPDATE statement

But as far as I know, SqLite does not support variables.

How can I rewrite the MySQL query from above for SqLite?

Answer 1

You need to declare and use variables in any program you write that runs SQLite statements .

Fortunately, you can use bind variables in SQLite :

 //1. Fetch the id into whatever language you are using: SELECT id FROM DATA WHERE status = 'scheduled' LIMIT 1; //2. Retrieve the id value from Step 1 resultset int id = [id value from the resultset]; //3. Construct the update statement parsed_sql_stmt stmt = parse_sql(UPDATE DATA SET STATUS = 'running' WHERE ID = ?); //4. Execute update statement exec_stmt(stmt, id); //5. Select everything in the DATA table for that record stmt = parse_sql(SELECT * FROM DATA WHERE id = ?); exec_stmt(stmt, id); 

sheepsimulator rights - these are three separate statements.

Answer 2

The answer to this question depends on where you are executing the code.

If you want to use variables when there is no command line interface for SQLite, there are no session variables. You were unlucky.

If you want to use variables when connecting to the SQLite database through the official C ++ API (or some other API), you will need to use bind variables (see 3.0 on this page).