Syntax error with IF EXISTS UPDATE ELSE INSERT (NODE JS)

Question

I am trying to write an SQL query that will update an entry if it exists and insert a new one if it does not exist. The UPDATE ON DUPLICATE KEY option doesn't work because I am not querying by the primary key. The SQL statement I am referring to is in the sql.query() function below. I have also added the error message.

  Asset.create = (newAsset, result) => {
      sql.query(
        `if exists(SELECT * from asset WHERE 
         AssetId="${newAsset.AssetId}" AND 
         AccountID="${newAsset.AccountId}") BEGIN UPDATE asset set 
         Amount="${newAsset.Amount}" where AssetId="${newAsset.AssetId}" 
         AND AccountID="${newAsset.AccountId}" End else begin INSERT 
         INTO asset SET ? end`,
        newAsset,
        (err, res) => {
          if (err) {
            console.log("error", err);
            result(err, null);
            return;
          }
    
          result(null, { id: res.insertId, ...newAsset });
        }
      );
    };

Error message:

sqlMessage: 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 exists(SELECT * from asset WHERE AssetId="bitcoin" AND AccountID="2c341fed-cf' at line 1,

such a construct is only allowed in functions and procedures and are **not** valid sql code — nbk, Jan 31 '22 at 13:48

score 1 · Accepted Answer · answered Jan 31 '22 at 16:33

In MySQL, compound statement syntax such as BEGIN ... END and IF ... THEN ... END IF is supported only in stored routines. You can't use such statements when executing them directly from clients.

https://dev.mysql.com/doc/refman/8.0/en/sql-compound-statements.html says:

This section describes the syntax for the BEGIN ... END compound statement and other statements that can be used in the body of stored programs: Stored procedures and functions, triggers, and events.

In the example you show, you seem to be trying to update a row, and if the row does not exist, then insert it.

One solution if you have a primary key or unique key on the table is to use INSERT ON DUPLICATE KEY UPDATE:

INSERT INTO asset (AssetId, AccountId, Amount) VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE Amount = VALUES(Amount);

I'm supposing for the above example that AssetId and AccountId are the primary key.

An alternative is to try the UPDATE first, and in the callback, check result.affectedRows. If this is zero, then try the INSERT.

AssetId and AccountId are not the primary keys which is why the ON DUPLICATE KEY UPDATE approach wouldn't work. However, your alternative approach worked perfectly. Thank you. I have been trying to figure this one out for days :) — Clancinio, Jan 31 '22 at 17:16

score 0 · Answer 2 · answered Jan 31 '22 at 14:02

this is no valid sql code

In SQL you would write something like

SELECT
if (exists(SELECT * from asset WHERE AssetId="${newAsset.AssetId}" AND AccountID="${newAsset.AccountId}") 
, @sql := 'UPDATE asset set Amount="${newAsset.Amount}" where AssetId="${newAsset.AssetId}" AND AccountID="${newAsset.AccountId}" ',
@sql := 'INSERT INTO asset SET ? ');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

but this is multi query which mus be eanabled

it needs still some work ys you have to many quotes

Syntax error with IF EXISTS UPDATE ELSE INSERT (NODE JS)

2 Answers2