CODEWITHSUNDEEP
sqlsqlite
Kenenbek Arzymatov
Kenenbek Arzymatov

Reputation: 9129

Return deleted rows in sqlite

It's not efficient to do two queries like SELECT * FROM TABLE WHERE clause and then DELETE * FROM TABLE WHERE clause.

So I want to make DELETE query and return deleted rows (one query).

I tried to do:

DELETE OUTPUT DELETED.*
FROM table
WHERE clause

But I have an error:

SQLite exception: near "OUTPUT": syntax error

How to make it correctly or maybe there is another alternative way to return deleted rows?

Upvotes: 15

Views: 8427

Answers (3)

CL.
CL.

Reputation: 180192

The DELETE statement has no OUTPUT clause.

After doing the SELECT, all the important data is in the cache, so the DELETE will run quickly.

And because SELECT plus DELETE is the only way, it is the most efficient way.

Since version 3.35, SQLite has the RETURNING clause.

Upvotes: 13

gerardnico
gerardnico

Reputation: 1073

You can return the records deleted with the returning clause

delete from myTable returning *

Upvotes: 9

Yvan
Yvan

Reputation: 2699

You can use the changes() call right after your DELETE query: https://www.sqlite.org/c3ref/changes.html

Implementation will vary depending on the language you're using. For example in PHP you could write:

$sqlite = new SQLite3('database.sqlite');
$statement = $sqlite->prepare('DELETE FROM mytable WHERE myclause');
$statement->execute();
echo $sqlite->changes();

Upvotes: 1

Related Questions