SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
05-10-2008, 02:03 PM
| ||||
| ||||
 Merging data in two tables 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. Thanks!      |
|
05-10-2008, 02:03 PM
| |||
| |||
| Re: Merging data in two tables 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] |
|
05-13-2008, 06:13 PM
| |||
| |||
| Re: Merging data in two tables 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! |
|
05-13-2008, 06:13 PM
| ||||
| ||||
| Re: Merging data in two tables On Mon, 12 May 2008 18:05:02 +0200, Shane <shane.pinnell@gmail.com> wrote: > 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). > > 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... When possible (and it almost always is), list every column in every SQL 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] |
Linear Mode
