CODEWITHSUNDEEP
csqlitesql-update
sudo
sudo

Reputation: 5784

Getting the Rows Affected by UPDATE in SQLite3 without Extra Query

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.

Upvotes: 2

Views: 7327

Answers (2)

shilch
shilch

Reputation: 1935

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 *;

Upvotes: 10

CL.
CL.

Reputation: 180020

The UPDATE statement does not return anything, so this is not possible.

Using a SELECT statement immediately afterwards does not hurt; the rows will still be in the cache.

Upvotes: 3

Related Questions