[sqlite] optimizing a multiple select statement
Hello, i am doing this request and was wondering if I can coerce it, as all tables of the subset (the cl* tables) all have the same fields. As you can see, i need to sort them by lomi,loma,lami,lama and ID. insert into idlookup select * from cl1 where (lomi>13.96 and loma<13.96 and lami>53.23 and lama<53.23) and id not in (select id from idlookup) UNION select * from cl2 where (lomi>13.96 and loma<13.96 and lami>53.23 and lama<53.23) and id not in (select id from idlookup) UNION select * from cl3 where (lomi>13.96 and loma<13.96 and lami>53.23 and lama<53.23) and id not in (select id from idlookup) UNION select * from cl4 where (lomi>13.96 and loma<13.96 and lami>53.23 and lama<53.23) and id not in (select id from idlookup) UNION select * from cl5 where (lomi>13.96 and loma<13.96 and lami>53.23 and lama<53.23) and id not in (select id from idlookup)" Sorry if this is trivial, but is there no way to do something like: insert into idlookup ( ( select * from cl1 UNION select * from cl2 UNION select * from cl3 UNION select * from cl4 UNION select * from cl5 ) where (lomi>13.96 and loma<13.96 and lami>53.23 and lama<53.23) and id not in (select id from idlookup) ) ? -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] optimizing a multiple select statement
"Christophe Leske" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Sorry if this is trivial, but is there no way to do something like: > > insert into idlookup > ( > ( > select * from cl1 > UNION select * from cl2 > UNION select * from cl3 > UNION select * from cl4 > UNION select * from cl5 > ) > where (lomi>13.96 and loma<13.96 and lami>53.23 and lama<53.23) and id > not in (select id from idlookup) > ) Try this: insert or ignore into idlookup select * from ( select * from cl1 UNION ALL select * from cl2 UNION ALL select * from cl3 UNION ALL select * from cl4 UNION ALL select * from cl5 ) where (lomi>13.96 and loma<13.96 and lami>53.23 and lama<53.23); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] optimizing a multiple select statement
> insert or ignore into idlookup > select * from ( > select * from cl1 > UNION ALL select * from cl2 > UNION ALL select * from cl3 > UNION ALL select * from cl4 > UNION ALL select * from cl5 > ) > where (lomi>13.96 and loma<13.96 and lami>53.23 and lama<53.23); > Thanks, but sometimes it says "constraint failed"? What does this mean? Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
