Check if row exists, if not add it [MySQL]

Question

I need to add a row to a MySQL database table but only if the row doesn't already exist. My database server just went down so I can't test this, but will this work as expected?

INSERT INTO `blocks` (`block_file`,`settings_group`)
VALUES ('announcements','announcement_settings')
WHERE NOT EXISTS (SELECT `block_file`,`settings_group`
                  FROM `blocks`
                  WHERE `block_file`='announcements' AND `settings_group`='announcement_settings')

It seems like sound logic. Is this a valid query or is there a better way of doing this?

In SQL Server that type of approach would give you a race condition. Not sure whether this is true for MySQL as well. Is it just this specific query or do you always want to enforce uniqueness on those 2 columns? — Martin Smith
– Martin Smith, Commented Feb 8, 2011 at 23:26
Same with mysql - you cannot modify a table while you are selecting from it — Marc B
– Marc B, Commented Feb 8, 2011 at 23:34

Mchl · Accepted Answer · 2011-02-08 23:27:22Z

3

Just create UNIQUE index on (block_file,settings_group) columns, and MySQL will never let you insert a row that would duplicate these values.

And to answer the question: No, it will not work at all.

answered Feb 8, 2011 at 23:27

Mchl

62.4k9 gold badges121 silver badges121 bronze badges

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

2 Comments

Jose Armesto Over a year ago

And if you need to insert several records, just use the IGNORE keyword, so if you try to insert a duplicate row, the error would be treated as warning. Check the sintax dev.mysql.com/doc/refman/5.0/es/insert.html

Steven Over a year ago

Thanks! Works just as expected and much cleaner.

Collectives™ on Stack Overflow

Check if row exists, if not add it [MySQL]

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related