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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
