From: Sebastian Mendel (listssebastianmendel.de)
Date: Tue Mar 11 2008 - 08:53:03 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]