Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

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.

share|improve this answer
    
Name is already set as the unique key. Also, this creates duplicates. –  AaronJAnderson Dec 24 '11 at 19:34
    
This cannot possibly create duplicates. INSERT IGNORE bars INSERTs from Duplicates by means of the PRIMARY KEY and other UNIQUE KEYs. Now, if you mean Duplicate Indexes, then, yes you are correct. –  RolandoMySQLDBA Dec 24 '11 at 19:39

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

share|improve this answer
    
This isn't what I'm looking for. –  AaronJAnderson Dec 24 '11 at 19:34
up vote 1 down vote accepted
INSERT INTO AdminAccounts 
    (Name)
SELECT t1.name
  FROM Matrix t1
 WHERE NOT EXISTS(SELECT id
                    FROM AdminAccounts t2
                   WHERE t2.Name = t1.Name)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.