MySQL - Inserting rows while avoiding duplicatiions

Question

I currently have two tables. One fairly large and one suitably small. The large table is made up of many inserts from tables in the same style as the small one.

I want to insert (probably using insert into) the records from the small table into the big table IF they are not already present in the big table.

Points of Note

Both tables have identical column titles so my attempts at joins aren't working (Error Code 1060 - see http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins under 'Left-Excluding Join' for the syntax I was using)
The tables do not have (and can not be given) a primary key / Unique Key
Rows are identical if and only if each column store the same value

4th point of note should be that you're offering a bounty for slapping the moron who designed such a table. No primary key? Good luck hacking this, I know I'd beat the living s*** out of the idiot who made me work with such a mess. — N.B.
– N.B., Commented Aug 19, 2015 at 11:26

Gordon Linoff · Accepted Answer · 2015-08-19 11:29:11Z

1

You can do this with not exists:

insert into bigTable(col1, . . ., coln)
   select col1, . . ., coln
   from smallTable s
   where not exists (select 1
                     from bigTable b
                     where b.col1 = s.col1 and b.col2 = s.col2 and . . .
                    );

Note: this does not work for NULL values. Well, it does "work", but you will get duplicate rows, which is consistent with the meaning of NULL as "unknown" (as opposed to "missing").

answered Aug 19, 2015 at 11:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

draco_alpine Over a year ago

Thank you, that worked perfectly (and the hint about NULL results is very useful as well!)

Collectives™ on Stack Overflow

MySQL - Inserting rows while avoiding duplicatiions

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related