 | |
#1
 
| |||
| |||
|
Merging data in two tables -
05-09-2008
, 07:14 PM
#2
| |||
| |||
|
#3
| |||
| |||
|
|
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. |
Quote:
|
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. |
(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]
#4
| |||
| |||
|
|
On Sat, 10 May 2008 05:16:47 +0200, macca <ptmcna... (AT) googlemail (DOT) 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] |
avoid listing each column, but if I must it isn't a big deal...
Thanks!
#5
| |||
| |||
|
|
On May 9, 9:44*pm, "Rik Wasmus" <luiheidsgoe... (AT) hotmail (DOT) com> wrote: On Sat, 10 May 2008 05:16:47 +0200, macca <ptmcna... (AT) googlemail (DOT) 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). 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... |
statement specifically. Any alteration in the table on which possible code
depends will fail then and there, instead of later on in a possible very
obscure, hard to track way.
--
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]
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.