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