Re: Performance problem with DELETE FROM/correlated subqueries

On Fri, 7 Feb 2020 at 16:25, Clemens Ladisch <[email protected]> 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 main.att2 = staging.att2)
> >
> > which takes a very long time.
>
> DELETE FROM main WHERE (att1, att2) IN (SELECT att1, att2 FROM staging);
>

Note using row-values requires sqlite 3.15.0 or later -- which is three
years old at this point, but every version I have on hand still says
'Error: near ",": syntax error' so I thought I'd track down the details :)
-Rowan
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users