MySQL ON DUPLICATE KEY UPDATE in NodeJS

Question

I'm using NodeJS with MySQL and async/await statements. I've got a UNIQUE column in a MySQL table named audioid.

I need to check if an audioid exists. If so I need to update a row. If not I need to insert a row. I'm using MySQL's So here's the query;

 try {


  if(tag1){ 

    row_b = await db.query( "SELECT tagid FROM tags WHERE tagname = ?", [tag1]);    

  if (row_b > 0){   
    
    const tagid1 = row_b[0].tagid;
    console.log('tagid first = ' + tagid1);        
    row_c = await db.query(
   "INSERT INTO entitytag (tagid1) VALUES (?) WHERE audioid = ? 
    ON DUPLICATE KEY UPDATE tagid1 = ?", [tagid1, audioid, tagid1]
                           );
                  } 
    else {  
    row_d = await db.query( 'INSERT IGNORE INTO tags (tagname) VALUES (?)', [tag1]);            
    const tagid1 = row_d.insertId;
     console.log('tagid 2nd = ' + tagid1);         
    row_e = await db.query(
    "INSERT INTO entitytag (tagid1) VALUES (?) WHERE audioid = ? 
    ON DUPLICATE KEY UPDATE tagid1 = ?", [tagid1, audioid, tagid1]
                          );
           }
        
    console.log('success!');
    res.json('success!');                       
    }       
  }

But there's the error in the console;

[ RowDataPacket { tagid: 11 } ] tagid 2nd = 0 ER_PARSE_ERROR: 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 'WHERE audioid = 26 ON DUPLICATE KEY UPDATE tagid1 = 0' at line 1

@lelio-faieta comments and answer are correct with regards to the `ON DUPLICATE KEY UPDATE` queries. based on your example code it looks like your goal is to maintain key values across related tables. you might also consider leveraging [foreign keys and referential actions](https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html), [tablet riggers](https://dev.mysql.com/doc/refman/8.0/en/triggers.html), stored procedures, or any combination of those options. They might save you some time and resources depending on your architecture and throughput for this specific case. — dusthaines, Jul 06 '21 at 13:29

Lelio Faieta · Accepted Answer · 2021-07-06T13:14:46.713

2

INSERT INTO entitytag (tagid1) VALUES (?) WHERE audioid = ? 
ON DUPLICATE KEY UPDATE tagid1 = ?

is wrong on a SQL basis since insert creates a new row, so there are no WHERE conditions applicable. If you want to specify that the duplicate check should happen on audioid then you should create an index on the table with UNIQUE attribute on that field.

The correct query (from an syntax standpoint only) is

INSERT INTO entitytag (tagid1) VALUES (?) 
ON DUPLICATE KEY UPDATE tagid1 = ?

Without having sample data,expected results and table structures it is a matter of guessing but a working (functionally) query could be:

INSERT INTO entitytag (tagid1, audioid) VALUES (?,?) 
ON DUPLICATE KEY UPDATE tagid1 = ?

edited Jul 06 '21 at 13:14

answered Jul 06 '21 at 13:01

Lelio Faieta

6,457
7
40
74

That mustve worked because the error has now hanged to ; [ RowDataPacket { tagid: 11 } ] row_d = [object Object] Cannot read property 'tagid' of undefined – Meggy Jul 06 '21 at 13:22
1

that is a problem of the data you are passing to the query, not the sql syntax and this is a completely different question – Lelio Faieta Jul 06 '21 at 13:24
Any idea how to access that last insertId? – Meggy Jul 06 '21 at 13:24
1

See [this SO question](https://stackoverflow.com/questions/31371079/retrieve-last-inserted-id-with-mysql) for this – Lelio Faieta Jul 06 '21 at 13:27
The only problem with that is I'm not using the usual function (error, results) callback. Instead I'm using row_a await. – Meggy Jul 06 '21 at 14:03
The package does not work that way - `.query()` is a function, in its simplest form it requires two arguments: the query string, and the callback. [If you require asynchronous behavior, see this example I provided to a similar question last week.](https://stackoverflow.com/questions/68231375) – dusthaines Jul 06 '21 at 15:23

MySQL ON DUPLICATE KEY UPDATE in NodeJS

1 Answers1