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
