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