UPDATE or REPLACE problem

[BPdV]

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

[longneck]

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.

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;

[BPdV]

Thanks Longneck!

Working great!