Update one table from matches in another

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Update one table from matches in another

Flakheart
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.
Reply | Threaded
Open this post in threaded view
|

Re: Update one table from matches in another

Martin Engelschalk
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Update one table from matches in another

Simon Slavin-3

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Update one table from matches in another

Flakheart
In reply to this post by Martin Engelschalk
>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.
Reply | Threaded
Open this post in threaded view
|

Re: Update one table from matches in another

Igor Tandetnik
In reply to this post by Flakheart
flakpit <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Update one table from matches in another

Flakheart
>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.