How to break subquery into 2 simple query

Question

Hi ,

I am trying some way to optimize following sql statement:

exe_sql "DELETE FROM tblEvent_type WHERE eguid in (SELECT rowid FROM tblEvent_basic WHERE sguid=11);";
exe_sql "DELETE FROM tblEvent_group WHERE eguid in (SELECT rowid FROM tblEvent_basic WHERE sguid=11);";

It is said that sqlite3 did not perform well in subquery and notice that above two sql excuted "(SELECT rowid FROM tblEvent_basic WHERE sguid=11)"` twice ,so I'd like to try to split the subquery to something like below:

result =  exe_sql "(SELECT rowid FROM tblEvent_basic WHERE sguid=11);";
          exe_sql "DELETE FROM tblEvent_type WHERE eguid in  (result)
          exe_sql "DELETE FROM tblEvent_group WHERE eguid in (result)

How could achieve this? I have no idea how to get the parmater (result) binding to follwing statment in sqlite.

"DELETE FROM tblEvent_group WHERE eguid in (?) #how to bind result here

I am using sqlite3 C API directly.

Answer 1

You actually need Common Table Expressions (CTE), but that's not supported in SQLite.

Another option would be to store the result of the first query in a temporary table and then use that table in both delete statements:

CREATE TEMP TABLE items AS SELECT rowid FROM tblEvent_basic WHERE sguid=11

DELETE FROM tblEvent_type WHERE eguid in  (select rowid from items)
DELETE FROM tblEvent_group WHERE eguid in (select rowid from items)

DROP TABLE items

The DROP TABLE is optional, because the table only exists for the duration of the connection to the database.