SEO

#1 (**permalink**) 03-17-2008, 06:12 AM

I have a products table with historical price information. Some
records are missing price information. I added another field -
closest_price, to be populated for records with 0 price. This would
be price values from the same table, same product with non-zero price
with earliest date.

So my update statement looks like this:

update t1 a,
(select price_date, product_id, price from t1 group by product_id
having price_date = min(price_date) and price != 0 ) b
set a.closest_price = b.price
where a.product_id = b.product_id
and a.price = 0;

This statement doesn't work. I don't get error - just 0 rows updated.
I do get results from b if I ran it on its own.

Appreciate any help

#2 (**permalink**) 03-17-2008, 06:13 AM

MariSok schrieb:
> I have a products table with historical price information. Some
> records are missing price information. I added another field -
> closest_price, to be populated for records with 0 price. This would
> be price values from the same table, same product with non-zero price
> with earliest date.
>
>
> So my update statement looks like this:
>
> update t1 a,
> (select price_date, product_id, price from t1 group by product_id
> having price_date = min(price_date) and price != 0 ) b
> set a.closest_price = b.price
> where a.product_id = b.product_id
> and a.price = 0;
>
> This statement doesn't work. I don't get error - just 0 rows updated.
> I do get results from b if I ran it on its own.
>
> Appreciate any help

try:

UPDATE t1 a
SET a.closest_price =
(
SELECT b.price
FROM t1 b
WHERE b.price != 0
AND b.product_id = a.product_id
ORDER BY b.price_date DESC
LIMIT 1
)
WHERE a.price = 0;

--
Sebastian

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Updating rows in a table with the information from the same table