Getting the Rows Affected by UPDATE in SQLite3 without Extra Query

c, sql-update, sqlite

Solution

Since version 3.35.0 (2021-03-12), SQLite includes a non-standard RETURNING clause that allows for what you are looking for. In your case, you would just write:

UPDATE "ACTIVE"
SET confirmed2 = '%d'
WHERE username2 = '%s' AND activity = '%d'
RETURNING *;

Problem

Instead of using `SELECT` followed by `UPDATE` in a function I'm calling pretty frequently, is there a way to update rows in an SQLite3 database and get those updated rows without a second query or doing anything else that is slower than `SELECT` followed by `UPDATE`? Using triggers and an intermediate table doesn't count because it would still be slower than what I want. I'm using the standard `sqlite3` C library. Example: ``` UPDATE "ACTIVE" SET confirmed2 = '%d' WHERE username2 = '%s' AND activity = '%d' ``` I'm updating the "ACTIVE" table's rows. I also want to retrieve all rows where "username2" and "activity" are a certain pair of values so I can check another column's values, "username1". Since the `UPDATE` command is already querying those rows to update them, there should (but maybe isn't) a way for me to get those rows without making it query again. I can get the number of rows affected, but I can't find anything about getting the rows' data.

Original source