Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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