MYSQL, Insert/Update, if else statement

Question

I'm using PHP with mysqli and in this example I'm trying to update the price if the username and lastname exists in the database, else I want to insert a new user. Why is this not working?

IF (SELECT name,lastname FROM peoplePrice WHERE name='gus' AND lastname='lw') THEN
    UPDATE peoplePrice SET price='20' WHERE name='gus' AND lastname='lw';
ELSE
    INSERT INTO peoplePrice (name,lastname,price) 
    VALUES ('nisse','johansson','20');
END IF;

`SELECT` returns rows. Have a look at http://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists — PM 77-1, Apr 03 '16 at 21:38
Are you just inserting a random user, or should `name` and `lastname` match the ones used in the `SELECT`? — Arjan, Apr 03 '16 at 21:47
you can do the condition checking in php, and then execute the related queries. — wolfsgang, Apr 03 '16 at 22:36
@wolfsgang This would not be a good idea. If different instances of the script run in parallel, there could be raising conditions resulting in multiple attempts to create rows for the same person. — NineBerry, Apr 03 '16 at 22:46

score 2 · Answer 1 · answered Apr 03 '16 at 22:30

MySQL doesn't offer the kind of conditional logic you're using except in stored procedures. You should investigate INSERT ... ON DUPLICATE KEY UPDATE to do what you're trying to do. Read this: http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

MYSQL, Insert/Update, if else statement

1 Answers1