update mysql value based on previous date value -

i have mysql table following structure

id  name_id  class_id  currency     date       value  change 1    bill                0     04-01-2013    10.00    0 5    bill                0     04-02-2013     9.90    0 12   fred                0     04-01-2013     8.00    0 13   fred        b         1     04-02-2013     8.50    0   22   fred        b         1     04-03-2013     8.51    0   

i want update change column based on difference between day's value , previous date value when name_id , class_id , , currency equal . . . in small excerpt row 2 updated -0.10and row 5 0.01.

there 400,000 rows have auto-incremented id ids not in kind of order. there weekend , holidays dates missing, not sequential dates.

i seem able show change query this

select pd.name_id,    pd.class_id,    pd.currency,     pd.date,    pd.value,    round(pd.nav - (select nav               price_data x               x.date       < pd.date               , x.name_id      = pd.name_id               , x.class_id     = pd.class_id               , x.currency     = pd.currency                order price_date desc               limit 1),5) change price_data pd 

i've tried modify update, keep getting mysql error says #1093 - can't specify target table 'pd' update in clause , i'm not sure right approach. easier using php?

update price_data pd set pd.change = (    pd.value - (select value               price_data x               x.date       < pd.date               , x.fund_id      = pd.fund_id               , x.class_id     = pd.class_id               , x.currency     = pd.currency ) ) 

thanks might offer.

your task not easy one. so, decided split 2 requests: 1 select , 1 update.

the code made in php , based upon data, provided in fiddle:

<?php try {       $username = 'user';       $password = '';       $conn = new pdo('mysql:host=localhost;dbname=test', $username, $password);       $conn->setattribute(pdo::attr_errmode, pdo::errmode_exception);        $stmt = $conn->prepare(' select pd.id,    round(pd.value - (select value               price_data x               x.price_date       < pd.price_date               , x.fund_id      = pd.fund_id               , x.class_id     = pd.class_id               , x.currency_id     = pd.currency_id order x.price_date desc limit 1 ),5) `change` price_data pd     pd.`value_change`=0 ');       $stmt->execute();        $result = array();       while($row = $stmt->fetch(pdo::fetch_assoc)) {             if (!is_null($row["change"]))             {                   var_dump($row);                   $stmt2 = $conn->prepare(' update     price_data set     price_data.value_change=:change     price_data.id=:id ');                   $stmt2->execute($row);             }       }   } catch(pdoexception $e) {       echo 'error: ' . $e->getmessage(); } ?> 

this updated necessary records on machine. let me know, how worked on yours.