Re: [sqlite] Performance problem with DELETE FROM/correlated subqueries

2020-02-07 Thread Jürgen Baier
Hi, On 07.02.20 09:25, Clemens Ladisch wrote: Jürgen Baier wrote: CREATE TABLE main ( ATT1 INT, ATT2 INT, PRIMARY KEY (ATT1,ATT2) ); CREATE TABLE staging ( ATT1 INT, ATT2 INT ); Then I execute DELETE FROM main WHERE EXISTS (SELECT 1 FROM staging WHERE main.att1 = staging.att1 AND

Re: [sqlite] Performance problem with DELETE FROM/correlated subqueries

2020-02-07 Thread Rowan Worth
On Fri, 7 Feb 2020 at 16:25, Clemens Ladisch wrote: > Jürgen Baier wrote: > > CREATE TABLE main ( ATT1 INT, ATT2 INT, PRIMARY KEY (ATT1,ATT2) ); > > CREATE TABLE staging ( ATT1 INT, ATT2 INT ); > > > > Then I execute > > > > DELETE FROM main WHERE EXISTS (SELECT 1 FROM staging WHERE

Re: [sqlite] Performance problem with DELETE FROM/correlated subqueries

2020-02-07 Thread Clemens Ladisch
Jürgen Baier wrote: > CREATE TABLE main ( ATT1 INT, ATT2 INT, PRIMARY KEY (ATT1,ATT2) ); > CREATE TABLE staging ( ATT1 INT, ATT2 INT ); > > Then I execute > > DELETE FROM main WHERE EXISTS (SELECT 1 FROM staging WHERE main.att1 = > staging.att1 AND main.att2 = staging.att2) > > which takes

[sqlite] Performance problem with DELETE FROM/correlated subqueries

2020-02-07 Thread Jürgen Baier
Hi, I have a question regarding the performance of DELETE FROM (or maybe better: correlated subqueries). I have a table "main" and a table "staging". In "staging" I have a subset of "main". I want to delete all rows from "main" which are also in "staging".   CREATE TABLE main ( ATT1 INT,