mysql conditional insert?

Question

I have trawled through tens of forums now to find MySql syntax to insert a row if there is no other row in the table that contains a give value.

I know it has to be simple but so far have found nothing that does what I need...

My requirement is simply:

if not exists (select * from table1 where int_value2 = 123) then insert into table1 (value1, int_value2, value3) values ('a', 1, 'a');

I apologise for how simple I know this is going to be but thanks in advance for any help you can offer.

Wrikken · Accepted Answer · 2011-04-05 23:46:08Z

2

~~Define a UNIQUE constraint if it not already exists:~~

ALTER TABLE table1  ADD UNIQUE(int_value2);

INSERT IGNORE INTO table1 (value1, int_value2, value3) VALUES ('a', 1, 'a');

Note the 'IGNORE` bit.

If you need 'fresh' data in value1/value3, you could look at ON DUPLICATE KEY UPDATE.

INSERT INTO table1 (value1, int_value2, value3)
SELECT 'a', 1, 'a' FROM DUAL 
WHERE NOT EXISTS(SELECT * FROM table1 WHERE int_value2=123);

answered Apr 5, 2011 at 23:24

Wrikken

70.8k8 gold badges99 silver badges136 bronze badges

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

2 Comments

The int_value2 being checked in the example pseudo-query isn't the same as the one that's being inserted.

Awesome thank you! I had a few variants of that query but that one works perfectly... Much appreciated.