List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:June 11 1999 6:29pm
Subject:Re: Simple thing that i should probably know ...
View as plain text  
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

Thread
Simple thing that i should probably know ...Ed Williams11 Jun
  • Re: Simple thing that i should probably know ...Jim Faucette11 Jun
  • Re: Simple thing that i should probably know ...Christian Mack11 Jun
  • Re: Simple thing that i should probably know ...Jim Faucette11 Jun
  • Re: Simple thing that i should probably know ...Christian Mack11 Jun
  • Re: Simple thing that i should probably know ...Vora Jinesh Udaykumar12 Jun
    • keeping the whole database in the main memory...Vora Jinesh Udaykumar12 Jun
RE: Simple thing that i should probably know ...Scott Cole12 Jun
  • Re: Simple thing that i should probably know ...Benjamin Pflugmann12 Jun