Update if row > 0 OR INSERT on Mysql

Question

How to I can create a query for update, if select result is bigger then 0, else execute insert?

I had try execute this code, but is not work

select
    case
        where count(id) > 0 (update product set description = 'blablabla' where id_fk1 = 3 AND id_fk2 = 4)
        else (insert into product (description) values('blablabla')
    end

I know this is a select code, it will not work, but I don't know how to use If in mysql

score 0 · Answer 1 · answered Mar 16 '14 at 23:43

0

you can use this example foreign-keys there is a way to compose single query which compose single query with 'update ...' and 'insert into...' or, if i misunderstood you, you can use this one insert-on-duplucate

answered Mar 16 '14 at 23:43

Paul Kotets

199
5

score 0 · Answer 2 · answered Mar 16 '14 at 23:43

0

What you need is INSERT with ON DUPLICATE KEY UPDATE clause:

https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

answered Mar 16 '14 at 23:43

Pawel Kozela

484
5
12

I can not use ON DUPLICATE KEY UPDATE, because my check condition is not Unique key column and I no have id of registers, is a combination of 2 columns (exemple: update tb1 set c1 = 'v1' where id_fk1 = 2 and id_fk2 = 3); not update tb1 set c1 = 'v1' where id = 3 – Lai32290 Mar 16 '14 at 23:45
1

I see. I don't think this is possible with a single statement then. – Pawel Kozela Mar 16 '14 at 23:59

score 0 · Answer 3 · edited May 23 '17 at 11:57

0

This has been asked before: Insert into a MySQL table or update if exists

Contains many interesting answers.

edited May 23 '17 at 11:57

Community

1
1

answered Mar 16 '14 at 23:46

ikkentim

1,639
15
30

Gordon Linoff · Answer 4 · 2014-03-17T00:48:54.413

Presumably, you want to insert the paid id = 3, description = 'blablabla' into the table. If it exists, then update the description.

You can use insert . . . on duplicate key insert, assuming that id is declared as the primary key or as a unique key. You would do this as:

create unique key product_id on product(id);

Then the following statement does what you want:

insert into product(id, description)
    select 3, 'blablabla'
    on duplicate key insert description = values(description);

EDIT:

I think the same idea applies to two foreign keys:

create unique key product_fk1_fk2 on product(id_fk1, id_fk2);

insert into product(id_fk1, id_fk2, description)
    select 3, 4, 'blablabla'
    on duplicate key insert description = values(description);

sorry for my bad expression, but I can not apply unique key for product_id, because this column is a many to one foreign key (product 1 - n tb2) — Lai32290, Mar 17 '14 at 00:42

Logan Wayne · Answer 5 · 2014-03-17T00:55:49.377

It will be base on the number of your query result, right?

$result=mysqli_query($con,"SELECT * FROM product");

if(mysqli_num_rows($result)==0){

mysqli_query($con,"INSERT INTO product (description) VALUES ('blah')");

}

else {

mysqli_query($con,"UPDATE product SET description='blah'");

}

If you're trying to base it on your id field, try this:

$result=mysqli_query($con,"SELECT * FROM product");

if(mysqli_num_rows($result)==0){

mysqli_query($con,"INSERT INTO product (description) VALUES ('blah')");

}

else 

while($row=mysqli_fetch_array($result)){

$id=$row['id'];

if($id>0){
mysqli_query($con,"UPDATE product SET description='blah' WHERE id='$id'");
}

} /* END OF WHILE LOOP */

} /* END OF ELSE */

Update if row > 0 OR INSERT on Mysql

5 Answers5