On Sat, Oct 2, 2010 at 4:02 PM, Igor Tandetnik <[email protected]> wrote:
> Fadhel Al-Hashim <[email protected]> wrote: > > I have two tables that contain about 5 million records. I am trying to > write > > an SQL command to delete rows from table A with PK (x,y,z) where PK > (x,y,z) > > is not in table B. > > > delete from A where rowid in > (select A.rowid from A left join B on (A.x = B.x and A.y = B.y and A.z = > B.z) > where B.x is null); > > Igor Tandetnik > > > Tried the query above (with left join and is null) and also one that looks a little more complex with test data: DELETE FROM A WHERE rowid in ( SELECT A.rowid FROM (SELECT a,b,c FROM A EXCEPT SELECT a,b,c FROM B ORDER BY a,b,c) t1 LEFT JOIN A ON t1.a=A.a and t1.b=A.b and t1.c=A.c ORDER BY A.rowid ) The latter is about 20-30% faster and reads a little fewer (tested with some 3.6.* version and 3.7.2), but I can't figure out why. EXCEPT looks like a good replacement for Left join with is Null but it can't retrieve other columns in the same query, that's why I needed extra join here. Nevertheless it still shows good results. Max _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
