Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

mysql get data from prior month

Posted on 2015-01-04
7
Medium Priority
?
187 Views
Last Modified: 2015-01-28
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
Comment
Question by:UniqueData
  • 3
  • 2
  • 2
7 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 40530979
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
 
LVL 7

Accepted Solution

by:
UniqueData earned 0 total points
ID: 40530980
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 40530982
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.

 
LVL 80

Expert Comment

by:arnold
ID: 40530989
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
 
LVL 80

Expert Comment

by:arnold
ID: 40530993
You are updating the whole table, limiting the scope to a one month period will reduce the number of updates.
0
 
LVL 7

Author Comment

by:UniqueData
ID: 40530997
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
 
LVL 7

Author Closing Comment

by:UniqueData
ID: 40574834
was able to find a solution
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question