0

I've got a table which holds 3 tags per audioid. These are columns named tagid1, tagid2 and tagid3. The default value of these in the database is NULL.

I'd like to check whether a row exists with tagid1. If it exists or is NULL I need to update the column. But if the row doesn't exist at all I need to insert a brand new row. Here's what I've tried which results in a duplicate inserted row instead of an updated row;

row_c = await db.query("SELECT tagid1 FROM entitytag WHERE audioid = ?", [audioid]);

if (row_c.length > 0) { 

    var etid1 = row_c[0].etid; 

    row_d = await db.query("UPDATE entitytag SET tagid1 = ? WHERE etid =?", [tagid1, etid1]);   

} else {

    row_e = await db.query("INSERT INTO entitytag  (audioid, tagid1, userid)  VALUES (?,?,?)", [audioid, tagid1, userid ]);     
}
Meggy
  • 1,491
  • 3
  • 28
  • 63
  • 2
    Use `INSERT .. ON DUPLICATE KEY UPDATE`. – Akina Jul 06 '21 at 10:32
  • What you are trying to achieve is sometimes referred as INSERT/UPDATE. Please check the link below for mysql tutorial. https://www.mysqltutorial.org/mysql-insert-or-update-on-duplicate-key-update/ – Rui Costa Jul 06 '21 at 11:00

0 Answers0