MySQL If exsists insert into or else do something else

Question

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')

You could take a look at the `replace into` feature? http://dev.mysql.com/doc/refman/5.0/en/replace.html — Nanne, Sep 20 '11 at 19:55

score 4 · Accepted Answer · edited May 23 '17 at 12:34

4

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

edited May 23 '17 at 12:34

Community

1
1

answered Sep 20 '11 at 20:56

Bill Karwin

538,548
86
673
828

John Hartsock · Answer 2 · 2011-09-20T20:29:50.560

3

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;

edited Sep 20 '11 at 20:29

answered Sep 20 '11 at 19:55

John Hartsock

85,422
23
131
146

Thanks, this looks more like what I want to do but I still get a sql syntax error =/ – Trey Sep 20 '11 at 20:18
It 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 – Trey Sep 20 '11 at 20:22
If 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. – John Hartsock Sep 20 '11 at 20:30
1

Yeah I noticed that, still doesn't work. It seems once the exist part comes in I start with the errors. – Trey Sep 20 '11 at 20:36
1

@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 http://stackoverflow.com/a/18288088 – Tushar Goswami Dec 10 '15 at 01:24

score 1 · Answer 3 · answered Mar 23 '14 at 14:24

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');

score 0 · Answer 4 · answered Sep 20 '11 at 20:02

Do you need to select the e-mail column only? Or do you have multiple columns that contain e-mail addresses? If you have multiple columns with email then you would need to say where col1Email = '$email' or col2Email='$email'... etc. To get a count of the rows try SELECT COUNT(*) from facebookqs where col1Email = '$email' or col2Email='$email'.

MySQL If exsists insert into or else do something else

4 Answers4

Linked