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, tablet riggers, 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
– dusthaines, Commented Jul 6, 2021 at 13:29

score 2 · Accepted Answer · 2021-07-06 13:14:46Z

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 6, 2021 at 13:14

answered Jul 6, 2021 at 13:01

user2399035

Sign up to request clarification or add additional context in comments.

6 Comments

Meggy Over a year ago

That mustve worked because the error has now hanged to ; [ RowDataPacket { tagid: 11 } ] row_d = [object Object] Cannot read property 'tagid' of undefined

user2399035 Over a year ago

that is a problem of the data you are passing to the query, not the sql syntax and this is a completely different question

Meggy Over a year ago

Any idea how to access that last insertId?

user2399035 Over a year ago

See this SO question for this

Meggy Over a year ago

The only problem with that is I'm not using the usual function (error, results) callback. Instead I'm using row_a await.

|

Collectives™ on Stack Overflow

MySQL ON DUPLICATE KEY UPDATE in NodeJS

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related