Mysql insert if condition

Question

I have a cat identifier relationship table - post id.

+----+--------+---------+ | id | cat_id | post_id | | | | | | 1 | 11 | 32 | | 2 | ... | ... | +----+--------+---------+ 

I use SELECT WHERE cat_id = 11 AND post_id = 32 , and then if the result is not found, I do INSERT . Can I rewrite these two queries in One?

Answer 1

You can do something like this:

 insert into cats_rel(cat_id, post_id) select 11, 32 where not exists (select 1 from cats_rel where cat_id = 11 and post_id = 32); 

EDIT:

Unfortunately. This does not work in MySQL because the from clause is missing (works in many other databases). In any case, I usually write this by putting values ​​in a subquery, so they only appear in the request once:

 insert into cats_rel(cat_id, post_id) select toinsert.cat_id, toinsert.post_id from (select 11 as cat_id, 32 as post_id) toinsert where not exists (select 1 from cats_rel cr where cr.cat_id = toinsert.cat_id and cr.post_id = toinsert.post_id ); 

Answer 2

You can use replace

 REPLACE INTO 'yourtable' SET `cat_id` = 11, `post_id` = 32; 

if the record exists, it will overwrite it, otherwise it will be created;

Update: To do this, you must add a unique key to a pair of columns, not only one

 ALTER TABLE yourtable ADD UNIQUE INDEX cat_post_unique (cat_id, post_id); 

Answer 3

We can use the "from dual" clause for MySQL:

 insert into cats_rel(cat_id, post_id) select 11, 32 from dual where not exists (select 1 from cats_rel where cat_id = 11 and post_id = 32);