|
|
Posted by ZeldorBlat on 08/07/07 15:53
On Aug 7, 11:38 am, "laredotorn...@zipmail.com"
<laredotorn...@zipmail.com> wrote:
> Hi,
>
> I'm running MySQL 5.0 lon Linux. I have two tables ...
>
> PRODUCTS
> -----------------
> ID INTEGER NOT NULL
> PRICE FLOAT UNSIGNED NOT NULL
> PRIMARY KEY (ID)
>
> CUSTOM_PRICES
> ---------------------------
> ID INTEGER NOT NULL
> PRODUCT_ID INTEGER NOT NULL
> PRICE FLOAT UNSIGNED NOT NULL
> PRIMARY KEY (ID)
> FOREIGN KEY PRODUCT_ID REFERENCES PRODUCTS(ID)
>
> The table "CUSTOM_PRICES" will have a subset of products from the
> products table. What I want to do is update the price in the PRODUCTS
> table with the corresponding entry from the CUSTOM_PRICES table,
> assuming there is one. If there is not a corresponding entry in the
> CUSTOM_PRICES table, I would like the price value in the PRODUCTS
> table to remain unchanged.
>
> How can I do this in a single UPDATE statement?
>
> Thanks, - Dave
update products
set price = c.price
from products p
join custom_prices c
on p.id = c.product_id
Of course that assumes there's only ever one price for any product in
your custom_prices table. According to your create table statement
that isn't enforced.
Navigation:
[Reply to this message]
|