Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


mysql get data from prior month

Posted on 2015-01-04
Medium Priority
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
Question by:UniqueData
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
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))

Accepted Solution

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
LVL 39

Expert Comment

by:Pratima Pharande
ID: 40530982
so is it worked or still having some error ?
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

LVL 79

Expert Comment

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)
LVL 79

Expert Comment

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

Author Comment

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

Author Closing Comment

ID: 40574834
was able to find a solution

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
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 Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA:…

721 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