On May 9, 9:44*pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Sat, 10 May 2008 05:16:47 +0200, macca <ptmcna...@googlemail.com> wrote:
>
> And forget to quote the OP:
>
> > I need to merge table "B" into table "A" inserting any new records and
> > updating any existing ones. Thetwotablesare identically formatted,
> > both with a key of "id" and identical columns.
>
> > I can do the insert of new records easily enough with INSERT IGNORE
> > INTO a (columns...) SELECT (columns...) FROM b.
>
> > It is the update of table "a" if, and only if, any column in table "b"
> > has changed that is stumping me.
>
> > I thought this was possible using a merge table, but I may be
> > incorrect. I have done this successfully in the past, but the exact
> > process escapes me.
>
> And answered:
>
> > How about REPLACE ?
>
> > REPLACE INTO a (columns...) SELECT (columns...) FROM b.
>
> > REPLACE is like INSERT except that it deletes old records as necessary
> > when a duplicate unique key value is present.
>
> Which would be a BAD idea if you happen to have FOREIGN KEY cascades *
> (worst case scenario, all relateddatain other or the same table could be *
> deleted). In MYSQL, an INSERT ..... ON DUPLICATE KEY UPDATE ... construct *
> will usually give you much more control, and would be very easy to *
> implement. MySQL will AFAIK not report rows set to the exactly same values*
> as updated if that's of importance.
>
> Of course, another possibility would be to just update based on a join *
> *from a to b with the same primary key and one of the fields different *
> (allthough you'd have to list _all_ columns of importance to the query in *
> the ON clause).
> --
> Rik Wasmus
> [SPAM] Now temporarily looking for some smaller PHP/MySQL projects/work to*
> fund a self developed bigger project, mail me at rik at rwasmus.nl. [/SPAM]

OK, I will look into the ON DUPLICATE KEY UPDATE, I was trying to
avoid listing each column, but if I must it isn't a big deal...

Thanks!