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

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

Answer 3

If you make the 'name' field in 'AdminAccounts' a primary key or unique index, you may simply use

REPLACE INTO AdminAccounts (Name) 
SELECT Name
FROM Matrix 

Thus, if a name doesn't exist in the table it will be inserted; if it exists, the old row is deleted and the new one is inserted.