Re: [sqlite] Update one table from matches in another
>You can do both in one pass: >update locations set ItemCount = >case when Location in (select Location from hive) > then '1' else '0' >end; Thanks for your help Igor, extremely elegant solution. -- View this message in context: http://old.nabble.com/Update-one-table-from-matches-in-another-tp32232409p32233560.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update one table from matches in another
flakpit wrote: > I was just using this to make sure my matches were going to be correct. > > select * from locations t1 where exists ( > select * from hive t2 > where t1.Location=t2.Location > ) A slightly shorter form: select * from locations where Location in ( select Location from hive); > Now, I need to update the ItemCount column in the locations table with > '1'when there is a corresponding match in the inventory table but using the > query below marks all locations in the locations table so I am out of my > depth here. > > > update locations > set ItemCount='1' > where exists( > select Location from hive t2 where Location=t2.Location > ) > > Anyone know how to adjust this? > > I also want to do the reverse, mark any ItemCount in the locations table > with '0' when there is no matching entry in the location column in the > inventory table > > update locations > set ItemCount='0' > where exists( > select Location from hive t2 where Location<>t2.Location > ) > > Have tried several varieties of these commands with errors, getting mixed > results. You can do both in one pass: update locations set ItemCount = case when Location in (select Location from hive) then '1' else '0' end; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update one table from matches in another
>update locations > set ItemCount='1' > where exists( > select Location from hive where locations.Location=hive.Location > ) Okay, seems that I wasn't too far off. Thank you very much for the help, I certainly needed it:):) >update locations > set ItemCount='0' > where NOT exists( > select Location from hive where locations.Location=hive.Location > ) I must be having a brain fade day, was sure that I had tried this. Ah well, living and learning as much as I can. Thanks once again. -- View this message in context: http://old.nabble.com/Update-one-table-from-matches-in-another-tp32232409p32233295.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update one table from matches in another
On 10 Aug 2011, at 10:27am, Martin Engelschalk wrote: > Am 10.08.2011 11:14, schrieb flakpit: >> I need to update the ItemCount column in the locations table with >> '1'when there is a corresponding match in the inventory table but using the >> query below marks all locations in the locations table so I am out of my >> depth here. >> >> >> update locations >> set ItemCount='1' >> where exists( >> select Location from hive t2 where Location=t2.Location >> ) > Try this: > > update locations > set ItemCount='1' > where exists( > select Location from hive where locations.Location=hive.Location > ) Also, there are SQLite functions which will count related records for you: http://www.sqlite.org/lang_aggfunc.html So you can do things like SELECT count(*) FROM hive WHERE hive.Location = locations.Location which will return the number of related records very quickly. Your form might be something like update locations set ItemCount = ( SELECT count(*) FROM hive WHERE hive.Location = locations.Location ) Speed on both Martin's and my forms will be improved if you have an index on the Location column of the hive TABLE. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update one table from matches in another
Hi, Am 10.08.2011 11:14, schrieb flakpit: > To see what matches the location table with locations in the inventory table, > I can issue the following command and it works returning 17 locations > matched and is correct. There are 21 locations in the locations table but > only 17 used in the inventory table so I know the query is returning correct > data. > > I was just using this to make sure my matches were going to be correct. > > select * from locations t1 where exists ( >select * from hive t2 >where t1.Location=t2.Location >) > > > Now, I need to update the ItemCount column in the locations table with > '1'when there is a corresponding match in the inventory table but using the > query below marks all locations in the locations table so I am out of my > depth here. > > > update locations >set ItemCount='1' >where exists( >select Location from hive t2 where Location=t2.Location >) Try this: update locations set ItemCount='1' where exists( select Location from hive where locations.Location=hive.Location ) > > Anyone know how to adjust this? > > I also want to do the reverse, mark any ItemCount in the locations table > with '0' when there is no matching entry in the location column in the > inventory table > > update locations >set ItemCount='0' >where exists( >select Location from hive t2 where Location<>t2.Location >) Try this: update locations set ItemCount='0' where NOT exists( select Location from hive where locations.Location=hive.Location ) > Have tried several varieties of these commands with errors, getting mixed > results. Martin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Update one table from matches in another
To see what matches the location table with locations in the inventory table, I can issue the following command and it works returning 17 locations matched and is correct. There are 21 locations in the locations table but only 17 used in the inventory table so I know the query is returning correct data. I was just using this to make sure my matches were going to be correct. select * from locations t1 where exists ( select * from hive t2 where t1.Location=t2.Location ) Now, I need to update the ItemCount column in the locations table with '1'when there is a corresponding match in the inventory table but using the query below marks all locations in the locations table so I am out of my depth here. update locations set ItemCount='1' where exists( select Location from hive t2 where Location=t2.Location ) Anyone know how to adjust this? I also want to do the reverse, mark any ItemCount in the locations table with '0' when there is no matching entry in the location column in the inventory table update locations set ItemCount='0' where exists( select Location from hive t2 where Location<>t2.Location ) Have tried several varieties of these commands with errors, getting mixed results. -- View this message in context: http://old.nabble.com/Update-one-table-from-matches-in-another-tp32232409p32232409.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
