Tell me more ×
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.
up vote -1 down vote favorite

I need to insert some records which might have duplicates. Column to overwrite/skip is mytable.email and mytable.fooFlag should be 1.

I came up with something like this: 

INSERT IGNORE INTO mytable  
VALUES ('somemailaddress')  
ONLY IF 'somemailaddress' DOES NOT EXIST  
AND fooFlag = 1
share|improve this question
Can you post the table's definition (CREATE TABLE ...)? – ypercube Nov 5 '12 at 17:58
Do you want the ONLY IF to apply to INSERT (only if) or to IGNORE (only if) – ypercube Dec 7 '12 at 11:31

2 Answers

up vote 0 down vote

If email is UNIQUE, that's what "INSERT IGNORE" is for!

If email is UNIQUE and you want to 'update' some other fields when it is already in the table, then that's what "INSERT ... ON DUPLICATE KEY UPDATE" is for.

share|improve this answer
up vote -1 down vote 
INSERT  INTO mytable (email)
SELECT sourcetable.email FROM sourcetable
WHERE sourcetable.email NOT IN (SELECT mytable.email from mytable where mytable.fooflag=1)
share|improve this answer
Can you add some detail about how or why this solves the problem? – Max Vernon Nov 5 '12 at 19:51

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.