SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Oct 2008
    Location
    Netherlands
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    UPDATE or REPLACE problem

    Hello and a very good 2009 to you all,
    I have a problem to update some records.
    I have a tables with date, code, origin. The most recent origin is the right one. I have to replace all the other origins (where the code is the same) with the right (most recent one)

    Amongst others I tried this:

    REPLACE INTO table t1 (origin)
    SELECT origin FROM table t2 WHERE t1.code = t2.code ORDER BY date DESC LIMIT 0,1

    But I'm too blind to see what's wrong I think

    Thanks in advance for any help

    Bauke

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    REPLACE is the wrong command to use. you need UPDATE instead.

    before you try this, make sure you have a backup and that you know how to restore it.
    Code:
    update t1
      join (select a
                 , max(b) as b
              from t1
            group
                by a) dt
        on t1.a = dt.a
       set t1.b = dt.b;
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SitePoint Member
    Join Date
    Oct 2008
    Location
    Netherlands
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Longneck!

    Working great!


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
  •