DELETE FROM table1 WHERE NOT EXISTS (SELECT 1 from tmp_keep WHERE id1 = table1.id1);
Does it in a single pass by doing a correlated subquery on each row in table1 to see if the id is in tmp_keep. > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Dave Blake > Sent: Friday, 3 March, 2017 03:53 > To: SQLite mailing list > Subject: [sqlite] Deleting records from a large table > > Say table1 has more then 500000 records, and there is a second table > tmp_keep with the ids of the records in table1 to be kept, the rest need > to > be deleted. The number of records in tmp_keep can vary from 0 to all the > records in table1, with any values in between. > > What is the best strategy for doing the deletion? > > For deleting a large number of records (tmp_keep is small), this works > fine: > DELETE FROM table1 WHERE id1 NOT IN (SELECT id1 FROM tmp_keep); > > But this becomes inefficient when tmp_keep is large. > > Any suggestions? > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
