Update Database if data exists else Insert in MySQL in a single step

Question

For my Application, I need to check whether data exists for some column. If exists, add a value 10 to the existing value else insert a new entry

I'm trying something like this

UPDATE reporting set column_name1 = column_name1 + 10 WHERE column_name2 = "201";
IF ROW_COUNT()=0 THEN
    INSERT INTO reporting (column_name1,column_name2) VALUES (15,201);
END IF

In my Table column_name2 is not a primary Key and I have disabled Safe checks.Due to this reason, I cannot use DUPLICATE KEY constraint.For this Query, I'm getting Syntax Error:

Error Code: 1064. 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 @@ROWCOUNT=0 THEN INSERT INTO reporting (identifier,_9_2017) ' at line 1

The syntax of If statement is similar to the one in MySQL Documentation https://dev.mysql.com/doc/refman/5.7/en/if.html. There are few Questions related to this in this portal but I couldn't find the right answer (UPDATE if exists else INSERT in SQL). Where am I going wrong here?

I don't know if this applies to your situation, but MySQL has a `REPLACE INTO` clause that updates if exists, and inserts if not. You can read here: https://dev.mysql.com/doc/refman/5.7/en/replace.html — Tuncay Göncüoğlu, Sep 04 '17 at 19:12

score 0 · Accepted Answer · answered Sep 04 '17 at 13:17

0

You should use ROW_COUNT() like this

SELECT ROW_COUNT();

answered Sep 04 '17 at 13:17

Narek Noreyan

26
1

Even after using ROW_COUNT(), Error still persists in IF Statement – Suraj Sep 04 '17 at 18:51

Update Database if data exists else Insert in MySQL in a single step

1 Answers1