NYCPHP Meetup

[Daniel Convissor]

On Mon, May 10, 2004 at 01:00:47PM -0400, Scott Mattocks wrote:
> 
> INSERT INTO table_b (field1, field2)
> (SELECT table_a.field1, table_a.field2
>  FROM table_a
>   LEFT JOIN table_b
>    ON (table_a.field1 = table_b.field1
>        AND
>        table_a.field2 = table_b.field2)
>  WHERE table_b.field1 IS NULL
>   AND table_b.field2 IS NULL)

While that would stop records already in table_b from being copied from
table_a, it would not stop duplicate records in table_a going into
table_b.  You could throw in a GROUP BY clause to take care of that.

All that aside, adding primary/unique keys is the way to go.

If tables already have duplicates but aren't keyed, like the original
poster said, then you've got to mysqldump the data, drop the old table,
change the table structure in the dumpped SQL data to add the indexes
needed and then rebuild/import the table/data.  Use the -f command line 
option that keeps SQL errors from stopping execution.

--Dan

-- 
 T H E   A N A L Y S I S   A N D   S O L U T I O N S   C O M P A N Y
            data intensive web and database programming
                http://www.AnalysisAndSolutions.com/
 4015 7th Ave #4, Brooklyn NY 11232  v: 718-854-0335 f: 718-854-0409