Node Mysql On Duplicate Key Update only updates unique row

Viewed 8627

I am using MySQL 5.7 with Node JS 6.11.0 and am trying to update a UNIQUE MySQL column whenever I insert a conflicting row. However, when I try inserting a conflicting record, only the existing record is updated to NULL and no insert happens. Here is my code

     pool.getConnection(function(err, connection) {
        var newClass = req.body;
        var query = `INSERT INTO classes SET ? ON DUPLICATE KEY UPDATE teacher_id = NULL`;

        connection.query(query, newClass, function(err, result) {   
            console.log(result);
            if(result.affectedRows >= 1) {
                res.status(201).end();
                res.json(result);
            }
        });
        connection.release();
    });`

I have to run the query twice for the row to be inserted; the first time the conflicting column is set to null then when I run the same query again, the row is inserted since there are no conflicts.

I have taken the SQL generated and directly run it from MySql console and I still have to run the query twice for the new row to be inserted. I do not understand why it is behaving this way.

Sql statement is

INSERT INTO classes SET `stream` = 'Red', `form` = '1', `teacher_id` = '7' ON DUPLICATE KEY UPDATE teacher_id = NULL

My create table SQL is

| classes | CREATE TABLE `classes` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `form` varchar(10) NOT NULL,
      `stream` varchar(15) NOT NULL,
      `teacher_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `teacher_id` (`teacher_id`),
      CONSTRAINT `classes_ibfk_1` FOREIGN KEY (`teacher_id`) REFERENCES `teachers` 
      ( `id` ) 
   ) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=latin1 |`

Why is MySQL behaving this way?

2 Answers
Related