On Sat, 10 May 2008 05:16:47 +0200, macca <ptmcnally@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. The two tables are 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 related data in 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]