CODEWITHSUNDEEP
databasesqlitesubquery
pierrotlefou
pierrotlefou

Reputation: 40721

How to break subquery into 2 simple query

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.

Upvotes: 0

Views: 868

Answers (1)

Philippe Leybaert
Philippe Leybaert

Reputation: 171744

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.

Upvotes: 1

Related Questions