How to use IF condition in mysql query

Question

Problem is simple,but it seems that each time I do something wrong in the mysql syntax. I want to search in a table after a String field. If this field exists, I will increase the number of apparitions.If does not exists, I will add a new row with it. I am trying to write a query which I will be used with jdbc or hibernate. I look at many examples in the site, but I fink that I am doing something wrong in the mysql syntax.

For the table:

mysql> select * from phrase;
+----+---------------+-------------------+
| id | phrase_string | apparition_number |
+----+---------------+-------------------+
|  1 | phrase 1      |                 2 |
|  2 | phrase 2      |                 1 |
|  3 | phrase 3      |                 5 |
|  4 | phrase 4      |                 6 |
+----+---------------+-------------------+
4 rows in set (0.00 sec)

I use the IF condition from the MYSQL tutorial: IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list] END IF

So my query looks like:

    **mysql> IF SELECT * FROM phrase WHERE phrase_string="phrase 1" THEN
        -> UPDATE phrase SET apparition_number=apparition_number+1
        -> ELSE
        -> INSERT INTO phrase VALUES(NULL,"phrase 1",1)
        -> END IF;**

ERROR 1064 (42000): 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 SELECT * FROM phrase WHERE phrase_string="
phrase 1" THEN
UPDATE phrase SET ap' at line 1

Can somebody tell me where I am wrong? Thank you!

that if clause is not valid as part of a select. just write two statements one for the update ( with a where clause ) and one for the insert ( again with where ) — amdixon
– amdixon, Commented Jan 10, 2016 at 7:18

Michal · Accepted Answer · 2016-01-10 07:45:31Z

1

The if syntax could be used only as part of stored procedure.

For this use case insert on duplicate key update can be used. Have a look at this SQL Fiddle.

edited Jan 10, 2016 at 7:45

answered Jan 10, 2016 at 7:21

Michal

2,4331 gold badge18 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

How to use IF condition in mysql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related