CODEWITHSUNDEEP
mysqlsql
user13283233
user13283233

Reputation:

Keep getting mySQL if statement syntax error. How do i fix it?

I keep getting an error. How do I run a query like this in MySQL?

UPDATE Events SET eventName = 'hi' IF ROW_COUNT() = 0 INSERT INTO EVENTS (eventName) VALUES (hi);

Basically I want to update but if there is no data to update, I insert the data. Is there a way to do it in one line?

Edit 1 Getting this error

mysql> UPDATE Events
    -> SET eventName = 'hi';

IF ROW_COUNT() = 0
THEN
    DELETE FROM Events
    WHERE EventID = 1;
END IF;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
mysql> IF ROW_COUNT() = 0
    -> THEN
    ->     DELETE FROM Events
    ->     WHERE EventID = 1;
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 ROW_COUNT() = 0
THEN
    DELETE FROM Events
    WHERE EventID = 1' at line 1
mysql> 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 'END IF' at line 1

Upvotes: 0

Views: 204

Answers (2)

James
James

Reputation: 351

I would use REPLACE, so for your code: MySQL REPLACE() replaces all the occurrences of a substring within a string.

REPLACE INTO Events SET eventName = 'hi'

Upvotes: 0

Barmar
Barmar

Reputation: 780724

In order to use an IF statement you have to be in a stored procedure (or other procedural context like a trigger or scheduled event). Then it has to be two statements and you need THEN and END IF:

UPDATE Events
SET eventName = 'hi';

IF ROW_COUNT() = 0
THEN
    DELETE FROM Events
    WHERE EventID = 1;
END IF;

Upvotes: 1

Related Questions