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
