SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Evangelist
    Join Date
    Jan 2005
    Location
    UK
    Posts
    539
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Update several rows with previous day's values

    I have a bunch of rows with a certain date that entered the mysql table as zeros. I would love to know a query that will loop through updating one date's values for every row (matching eg fieldname1 and fieldname2) with the previous date's values. I can do it manually or build a php script, but ideally a single mysql update statement would be handy!

    eg

    dateset fieldname1 fieldname2 value
    2013-01-20 a b 0
    2013-01-20 a c 0
    ......
    2013-01-19 a b 1.2345
    2013-01-19 a c 5.4321
    ......

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by skyline View Post
    ... ideally a single mysql update statement would be handy!
    you cannot update a table and reference the same table in the same statement

    besides, if you were able to do it, you would update the 20th's data to the 19th's, and the 19th's data to the 18th's, and so on... or even worse, update the 17th's data to the 18th's, and then the 18th's data to the 19th's, and then the 19th's data to the 20th's...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •