Обсуждение: [Newbie] UPDATE based on other table content

Поиск
Список
Период
Сортировка

[Newbie] UPDATE based on other table content

От
Daniel Chiaramello
Дата:
Hello.

I have a very basic question, relative to the following "problem".

I have the following tables:

product
    id
    qty

intermediate
    id
    product_id

orders
    intermediate_id

I want to update the "qty" field of the "product" table by incrementing
it each time there is an order in the "orders" table, referencing a
given product through the "intermediate" table.

I tried the following request:

UPDATE
        qty = qty+1
FROM
    intermediate,
    orders
WHERE
    orders.intermediate_id=intermediate.id AND
    intermediate.product_id=product.id
;

But of course it does what was predictable - ie the qty "field" is
incremented only once, even if more than one entry is referencing a
given product. But it's not what I was hoping...

What would be the "good" solution to do that UPDATE?

Thanks for your attention!
Daniel Chiaramello

Re: [Newbie] UPDATE based on other table content

От
Daniel Chiaramello
Дата:
Never mind, I found how finally:

UPDATE
    product
SET
    qty = qty+s_count
FROM (
        SELECT
            intermediate.product_id,
            count(*) AS s_count
        FROM
            intermediate,
            orders
        WHERE
            orders.intermediate_id=intermediate.id
        GROUP BY
            intermediate.product_id
    ) AS summary
WHERE
    summary.product_id = product.id
;

Sorry for disturbance!
Daniel

Daniel Chiaramello a écrit :
> Hello.
>
> I have a very basic question, relative to the following "problem".
>
> I have the following tables:
>
> product
>    id
>    qty
>
> intermediate
>    id
>    product_id
>
> orders
>    intermediate_id
>
> I want to update the "qty" field of the "product" table by
> incrementing it each time there is an order in the "orders" table,
> referencing a given product through the "intermediate" table.
>
> I tried the following request:
>
> UPDATE
>        qty = qty+1
> FROM
>    intermediate,
>    orders
> WHERE
>    orders.intermediate_id=intermediate.id AND
>    intermediate.product_id=product.id
> ;
>
> But of course it does what was predictable - ie the qty "field" is
> incremented only once, even if more than one entry is referencing a
> given product. But it's not what I was hoping...
>
> What would be the "good" solution to do that UPDATE?
>
> Thanks for your attention!
> Daniel Chiaramello
>


Re: [Newbie] UPDATE based on other table content

От
Thom Brown
Дата:
2009/10/29 Daniel Chiaramello <daniel.chiaramello@golog.net>:
> Never mind, I found how finally:
>
> UPDATE
>   product
> SET
>   qty = qty+s_count
> FROM (
>       SELECT
>           intermediate.product_id,
>           count(*) AS s_count
>       FROM
>           intermediate,
>           orders
>       WHERE
>           orders.intermediate_id=intermediate.id
>       GROUP BY
>           intermediate.product_id
>   ) AS summary
> WHERE
>   summary.product_id = product.id
> ;
>
> Sorry for disturbance!
> Daniel
>
> Daniel Chiaramello a écrit :
>>
>> Hello.
>>
>> I have a very basic question, relative to the following "problem".
>>
>> I have the following tables:
>>
>> product
>>   id
>>   qty
>>
>> intermediate
>>   id
>>   product_id
>>
>> orders
>>   intermediate_id
>>
>> I want to update the "qty" field of the "product" table by incrementing it
>> each time there is an order in the "orders" table, referencing a given
>> product through the "intermediate" table.
>>
>> I tried the following request:
>>
>> UPDATE
>>       qty = qty+1
>> FROM
>>   intermediate,
>>   orders
>> WHERE
>>   orders.intermediate_id=intermediate.id AND
>>   intermediate.product_id=product.id
>> ;
>>
>> But of course it does what was predictable - ie the qty "field" is
>> incremented only once, even if more than one entry is referencing a given
>> product. But it's not what I was hoping...
>>
>> What would be the "good" solution to do that UPDATE?
>>
>> Thanks for your attention!
>> Daniel Chiaramello
>>
>

Your solution looks like it would perform a cumulative calculation.
Surely you'd want qty = s_count?

In any case, wouldn't you be better off not having a quantity column
at all and just calculate it with either a query or a view?

Example:

SELECT product.id, COUNT(intermediate.product_id)
FROM product
LEFT JOIN intermediate ON product.id = intermediate.product_id
GROUP BY product.id
ORDER BY product.id

Or include a product name in the product table to get more meaningful
output.  You'd then get an output like:

id    name    count
1    Orange    5
2    Apples    7
3    Pears    2
4    Kiwi    0

If you don't want ones for which there have been no orders for (or
whatever your intermediate table is for), use an INNER JOIN instead.

Regards

Thom

Re: [Newbie] UPDATE based on other table content

От
Daniel Chiaramello
Дата:
Thom Brown a écrit :


...
> 2009/10/29 Daniel Chiaramello <daniel.chiaramello@golog.net>:
>
>> Never mind, I found how finally:
>>
>> UPDATE
>>   product
>> SET
>>   qty = qty+s_count
>> FROM (
>>       SELECT
>>           intermediate.product_id,
>>           count(*) AS s_count
>>       FROM
>>           intermediate,
>>           orders
>>       WHERE
>>           orders.intermediate_id=intermediate.id
>>       GROUP BY
>>           intermediate.product_id
>>   ) AS summary
>> WHERE
>>   summary.product_id = product.id
>> ;
>>
>> Sorry for disturbance!
>> Daniel
>>
>>
> Your solution looks like it would perform a cumulative calculation.
> Surely you'd want qty = s_count?
>
> In any case, wouldn't you be better off not having a quantity column
> at all and just calculate it with either a query or a view?
>
> Example:
>
> SELECT product.id, COUNT(intermediate.product_id)
> FROM product
> LEFT JOIN intermediate ON product.id = intermediate.product_id
> GROUP BY product.id
> ORDER BY product.id
>
> Or include a product name in the product table to get more meaningful
> output.  You'd then get an output like:
>
> id    name    count
> 1    Orange    5
> 2    Apples    7
> 3    Pears    2
> 4    Kiwi    0
>
> If you don't want ones for which there have been no orders for (or
> whatever your intermediate table is for), use an INNER JOIN instead.
>
> Regards
>
> Thom
>
>
Thanks for anwer, but no, it's really adding the number of entries to my
"qty" field. The "orders" table is a transient one and is cleaned regularly.
Of course, the example I gave is a simplified one (there are no orders
or products, I chose these names to ease the understanding of my
problem) - in reality, the problem is much complicated than that :)

But thanks for answer anyways.

Daniel