MySQL: Get results.insertId ON DUPLICATE KEY UPDATE

Question

I am developing an application that downloads images and their tags. When a download starts the program retrieves the tags and inserts them into the database. Here I am trying to insert a new tag and then create a relationship between the the tag and its download. The combination of name and type in tag is unique.

let download_id = 1;
let tag = {type:'language', name:'english'}

let sql = `INSERT INTO tag (name, type) SELECT '${tag.name}', id FROM tag_type WHERE type='${tag.type}' ON DUPLICATE KEY UPDATE count = count + 1, id=LAST_INSERT_ID(id)`
mysqlConnection.query(sql, (err, results) => {
    if (err) throw err;
    let sql = `INSERT INTO download_tag ?`;
    mysqlConnection.query(sql, [{download_id: download_id, tag_id: results.insertId}], err => {
        if (err) throw err;
    });
});

However my first query returns this error Uncaught Error: ER_NON_UNIQ_ERROR: Column 'id' in field list is ambiguous I am unsure why my code is not working, it is very similar to the accepted answer in this question.

score 0 · Accepted Answer · answered Apr 06 '19 at 07:29

Your problem is that LAST_INSERT_ID doesn't know whether you are referring to id from the tag table or from the tag_type table. You just need to qualify the field name with its table:

let sql = `INSERT INTO tag (name, type)
           SELECT '${tag.name}', id FROM tag_type WHERE type='${tag.type}' 
           ON DUPLICATE KEY UPDATE count = count + 1, id=LAST_INSERT_ID(tag.id)`

MySQL: Get results.insertId ON DUPLICATE KEY UPDATE

1 Answers1