asked on

mysql get data from prior month

I have a field called ColorCode and one called PriorColor. I am trying to update 'PriorColor' with the value of ColorCode from the month before (month is called 'Period').

This statement is not working:

UPDATE `dealerdata_new` SET `PriorColor` =
(Select ColorCode From `dealerdata_new` as  p where p.`Xtime_Dealer_Code` = dealerdata_new.`Xtime_Dealer_Code` and p.`Period`= 
 date_sub(dealerdata_new.`Period`, INTERVAL 1 MONTH))

Open in new window

I get the error: #1093 - You can't specify target table 'dealerdata_new' for update in FROM clause

Pratima

try this

UPDATE `dealerdata_new` as X
SET X.`PriorColor` =
(Select ColorCode From `dealerdata_new` as p where p.`Xtime_Dealer_Code` = X.`Xtime_Dealer_Code` and p.`Period`=
date_sub(X.`Period`, INTERVAL 1 MONTH))

ASKER CERTIFIED SOLUTION

UniqueData

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pratima

so is it worked or still having some error ?

arnold

Insert into dealerdata_new (PriorColor) values (select ColorCode from dealerdata_new where date_sub(period,interval 1 month) where (need to limit to current month to avoid all rows updating)

arnold

You are updating the whole table, limiting the scope to a one month period will reduce the number of updates.

UniqueData

ASKER

thanks arnold. this is a new field so the first time through I needed to update all records. But, yes, from this point forward, this field will be populated upon insert

UniqueData

ASKER

was able to find a solution