maria db insert or update

Question

I have the following sql code:

UPDATE google_calendar_accounts SET google_refresh_token="d",google_org_token="d" WHERE userID=5;
IF ROW_COUNT()=0 THEN
INSERT INTO google_calendar_accounts (userID,google_refresh_token,google_org_token) VALUES (5,"d","d"); END IF

and I am getting the error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 2

I am using mariadb 10.1.14

Answer 1

In spite the comment suggesting to do INSERT ... ON DUPLICATE KEY UPDATE ..., There may be a reason to do the update first, and insert just if there was no row affected, like the OP tried: this way auto increment won't be increased in vain.

So, a possible solution to the question may be using insert from select with a condition where row_count()=0

For example:

--first query
UPDATE google_calendar_accounts 
SET google_refresh_token='d',google_org_token='d' 
WHERE userID=5;
--second query use the affected rows of the previous query
INSERT IGNORE INTO google_calendar_accounts (userID,google_refresh_token,google_org_token)
SELECT 5,'d','d' WHERE ROW_COUNT()=0 

BTW: I've added IGNORE to the insert query for a case there was a row match to the update condition but it wasn't updated since all columns was identical to the updated, like in the case before the update there was already row 5,'d','d'. In such case, if the 5 is primary or unique key the query won't fail.