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))
I get the error: #1093 - You can't specify target table 'dealerdata_new' for update in FROM clause