INSERT INTO without duplicates

Question

I want to do this

INSERT INTO AdminAccounts (Name) 
SELECT Name
FROM Matrix 

But I don't want to create duplicates. ie, I ran this a few weeks ago and need to update the data.

Answer 1

This will insert new Name fields that were not previously in the Matrix table.

INSERT IGNORE
INTO AdminAccounts (Name) 
SELECT Name
FROM Matrix;

If you do not trust INSERT IGNORE, there is an alternative where you can manifest the new Name values before inserting them:

CREATE TABLE NewName SELECT Name FROM Matrix WHERE 1=2;
INSERT INTO NewName
    SELECT Name FROM AdminAccounts A
    LEFT JOIN Matrix B USING (Name)
    WHERE B.Name IS NULL;
INSERT INTO Matrix (Name) SELECT Name FROM NewNames;

The table NewName collects only those tables in AdminAccounts that are not in Matrix at present. This can give you a chance to look over the new Names. Afterwards, you can INSERT everything in NewName into Matrix.

Answer 2

This post should answer your question: http://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql

Syntax : Insert ... On Duplicate Key Update

Answer 3

INSERT INTO AdminAccounts 
    (Name)
SELECT t1.name
  FROM Matrix t1
 WHERE NOT EXISTS(SELECT id
                    FROM AdminAccounts t2
                   WHERE t2.Name = t1.Name)