5

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

Nick
  • 7,103
  • 2
  • 21
  • 43
Samuel
  • 1,073
  • 11
  • 22
  • If this is just in a normal query, that isn't going to work, conditionals and other constructs aren't supported outside of stored procedures. You may want to examine this related question: http://stackoverflow.com/questions/913841/mysql-conditional-insert – alzee Aug 02 '16 at 23:05
  • 5
    Or from the official docs, another upsert approach: https://mariadb.com/kb/en/mariadb/insert-on-duplicate-key-update/ – alzee Aug 02 '16 at 23:06

1 Answers1

4

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.

SHR
  • 7,940
  • 9
  • 38
  • 57