Jim Faucette wrote:
>
> It's true yours will work, but with the PRIMARY KEY so will mine. And I
> believe mine will be more efficient.
>
> Both styles will cause a key lookup for all table2 entries and an INSERT
> for any not found. As I understand REPLACE, your's will also cause a
> DELETE and INSERT for any that are already in table3.
>
> jim...
>
> Christian Mack wrote:
> >
> > Jim Faucette wrote:
> > >
> > > Ed Williams wrote:
> > > >
> > > > I have two identical tables with one column
> > > > all i want to do is merge them into one big table.
> > > > and get rid of the duplicates at the same time...
> > > > any ideas??
> > > > oh yeah the column names are identical in both tables but this can be
> changed.
> > > >
> > >
> > > CREATE table3 col_name same_def_as_others NOT NULL PRIMARY KEY;
> > > INSERT INTO table3 SELECT DISTINCT col_name FROM table1;
> > > INSERT INTO table3 SELECT DISTINCT col_name FROM table2;
> > >
> > > jim...
> >
> > Nope :)
> > Because table1 could contain a row identical to one in table2 use this:
> > CREATE table3 col_name same_def_as_others NOT NULL PRIMARY KEY;
> > INSERT INTO table3 SELECT DISTINCT col_name FROM table1;
> > REPLACE INTO table3 SELECT col_name FROM table2;
No, yours will not work, because if there are duplicates between the two tables, yours
will stop with a "duplicate key error".
But you are right with the additional overhead of deleting and inserting.
So the best solution should be:
CREATE table3 col_name same_def_as_others NOT NULL PRIMARY KEY;
INSERT INTO table3 SELECT DISTINCT col_name FROM table1;
INSERT IGNORE INTO table3 SELECT col_name FROM table2;
Tschau
Christian
