MySQL If exsists insert into or else do something else
Solution 1
Assuming email
has a UNIQUE
constraint, you should use INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO facebookqs (email, correct) VALUES ('$email', '$correct')
ON DUPLICATE KEY UPDATE correct = correct + '$correct'
See also my answer for this other Stack Overflow question: INSERT IGNORE vs INSERT … ON DUPLICATE KEY UPDATE
Solution 2
Your missing an EXISTS
statement and the THEN
and END IF
in the IF
IF (EXISTS(SELECT * FROM facebookqs WHERE email = '$email')) THEN
UPDATE facebookqs SET correct = correct + '$correct' WHERE email ='$email';
ELSE
INSERT INTO facebookqs (email, correct) VALUES ('$email', '$correct');
END IF;
Solution 3
Use the REPLACE
method, explained here: http://blogs.coldbuffer.com/inserting-or-updating-records-using-mysql-replace From their example:
REPLACE INTO pet (id, type, name) VALUES(1, 'Dog', 'Pluto');
which given your example should be (haven't tested it yet)
REPLACE INTO facebookqs (email, correct) VALUES ('$email', '$correct');
Related videos on Youtube
Comments
-
Trey almost 2 years
I'm having some difficulty putting a conditional into MySQL. I've been trying to create a query that will go through all of the column titled email and if it exists I want to do something like this: If an email exists I want it to take the existing value of the column correct and add the php variable $correct to it. But if an email does not exist then I want it to add a new record with the values $email into the column email and $correct into column correct. Any help would be greatly appreciated.
Here's what I have and does not work:IF (SELECT * FROM facebookqs WHERE email = '$email' > 0) UPDATE facebookqs SET correct = correct + '$correct' where email ='$email' Else Insert into facebookqs (email, correct) VALUES ('$email', '$correct')
-
Trey over 12 yearsThanks, this looks more like what I want to do but I still get a sql syntax error =/
-
Trey over 12 yearsIt says: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF (EXISTS(SELECT * FROM facebookqs WHERE email = 'test')) THEN UPDATE faceb' at line 1
-
John Hartsock over 12 yearsIf you notice i accidentally deleted the space between the WHERE clause in the update statement and the email columnname. If you copied my code word for word perhaps this is your problem.
-
Trey over 12 yearsYeah I noticed that, still doesn't work. It seems once the exist part comes in I start with the errors.
-
Tushar Goswami over 8 years@Trey Are you using this IF statement instead a stored procedure of mysql? I was doing the same mistake and was getting syntax error. You must create a stored procedure with this IF block inside it. See stackoverflow.com/a/18288088