flakpit <gary.flakhe...@gmail.com> 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
