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

Question

I want to update and select one row within one query in SqLite. In MySql my wanted query would 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 variable @update_id to the Id of the modified row for the first row that is having Status of 'scheduled' and than uses the variable @update_id to fetch the complete modified row.

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

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

How can the MySQL query from above be rewritten for SqLite?

Answer 1

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

Thankfully, 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's 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 is right - this is three separate statements.

Answer 2

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

If you want to use variables when in the commandline interface to SQLite, no, there are no session variables. Your out of luck there.

If you want to use variables when in a connection to an SQLite database via the official C++ API (or some other API), you'll need to use the bind variables notation (see 3.0 on that page).