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 stackoverflow.com/questions/4205181/… — PM 77-1
– PM 77-1, Commented Apr 3, 2016 at 21:38
Are you just inserting a random user, or should name and lastname match the ones used in the SELECT? — Arjan
– Arjan, Commented Apr 3, 2016 at 21:47
you can do the condition checking in php, and then execute the related queries. — wolfsgang
– wolfsgang, Commented Apr 3, 2016 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
– NineBerry, Commented Apr 3, 2016 at 22:46

O. Jones · Accepted Answer · 2016-04-03 22:30:51Z

2

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

answered Apr 3, 2016 at 22:30

O. Jones

109k17 gold badges134 silver badges187 bronze badges

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

Collectives™ on Stack Overflow

MYSQL, Insert/Update, if else statement

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related