CODEWITHSUNDEEP
mysqlsql
ptvty
ptvty

Reputation: 5664

MySQL Insert if Condition

I Have this cat id - post id relation table.

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

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

Upvotes: 4

Views: 9476

Answers (3)

Vladislav Sh
Vladislav Sh

Reputation: 1

We can use "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);

Upvotes: 0

mbouzahir
mbouzahir

Reputation: 1564

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 : For this to work you should add a unique key to the pair of columns not only one 

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

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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:

Oops. That above doesn't work in MySQL because it is missing a from clause (works in many other databases, though). In any case, I usually write this putting the values in a subquery, so they only appear in the query 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
                     );

Upvotes: 4

Related Questions