Update else INSERT in mysql

Question

I tested this code:

UPDATE books SET price='20000' WHERE user_id='2'
IF ROW_COUNT()=0
INSERT INTO store_books(name,user_id) VALUES ('test1','2')

I encountered the following error.

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 'IF ROW_COUNT()=0 INSERT INTO store_books(name,user_id) VALUES ('test1','2')' at line 2

Is there any solution to solve this problem?

I don't want to use INSERT INTO ... ON DUPLICATE KEY UPDATE (because i have multi keys in my main table).

Above example is trial for finding new way.

Pattern that i want: Update(if exists) ELSE Insert.

Welcome to Stack Overflow. You say you don't want to use INSERT ... ON DUPLICATE KEY UPDATE. Can you [edit] your question to explain why not? (Think of it as meaning UPDATE ... IF NOT EXISTS INSERT ... ) — O. Jones, Sep 14 '18 at 14:31
Why don't you want to use the tools built in to MySQL instead of trying to roll your own solution? — Dave, Sep 14 '18 at 14:40
@rahimi . . . Your question doesn't make sense. You are attempting an update in one table (`books`). If no row exists, you want to insert a row into *another* table, `store_books`. Please provide more information about what your data looks like and what you want to accomplish. — Gordon Linoff, Sep 14 '18 at 14:54
Visit this : https://stackoverflow.com/a/20310838/8896430 I think it can help you . — mohamad javad koshraftar, Sep 14 '18 at 16:15

score 1 · Accepted Answer · answered Sep 14 '18 at 14:41

Two things:

INSERT ... ON DUPLICATE KEY UPDATE ... is by far the best way to do what you want to do. If you don't do this, you'll have to use a database transaction to make sure you maintain integrity for the operation you want.
MySQL (unlike, say, MS SQL server) doesn't allow conditional execution of queries except in stored procedures. Read this. If conditional in SQL Script for Mysql

Update else INSERT in mysql

1 Answers1