Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 188
  • Last Modified:

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
0
UniqueData
Asked:
UniqueData
  • 3
  • 2
  • 2
1 Solution
 
Pratima PharandeCommented:
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))
0
 
UniqueDataAuthor Commented:
I had tried that before too.

I just thought to google the error and found a solution:
UPDATE `dealerdata_new` as c
      inner join `dealerdata_new`as p on
 p.`Xtime_Dealer_Code` = c.`Xtime_Dealer_Code` and p.`Period`=
 date_sub(c.`Period`, INTERVAL 1 MONTH)
SET c.`PriorColor` = p.ColorCode
0
 
Pratima PharandeCommented:
so is it worked or still having some error ?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
arnoldCommented:
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)
0
 
arnoldCommented:
You are updating the whole table, limiting the scope to a one month period will reduce the number of updates.
0
 
UniqueDataAuthor Commented:
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
0
 
UniqueDataAuthor Commented:
was able to find a solution
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now