mysql get data from prior month

Posted on 2015-01-04
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
  • 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 ?
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

LVL 78

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 78

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Suggested Solutions

Title # Comments Views Activity
remote mysql 8 35
Make login page safer 3 30
PHP SMTP authentication 6 23
Json encode, change how it looks like on when inserting to database 14 19
This article discusses four methods for overlaying images in a container on a web page
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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.

713 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