How can I write an SQL statement for this situation?

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

Re: How can I write an SQL statement for this situation?

On Aug 7, 11:38 am, "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.