Node Mysql On Duplicate Key Update only updates unique row

Question

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?

Answer 1

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

Is telling MySQL when there is a conflicting record (duplicate unique or primary key), to set the offending row's teacher_id column to null and stop.

You have a couple options to achieve your desired result.

First you can use the REPLACE syntax.

REPLACE INTO classes SET stream = 'Red', form = '1', teacher_id = '7';

Which will first delete the offending row with the UNIQUE or Primary Key constraint, and Insert a new row with the specified values. The drawback is any omitted column values would be lost and set to their default values. A new auto-generated (if any) id will also be created if not specified in the query. If there is no offending record a normal INSERT will occur.

Otherwise, you can utilize VALUES within the UPDATE clause.

INSERT INTO classes
        SET stream = 'Red', form = '1', teacher_id = '7'
    ON DUPLICATE KEY UPDATE stream = VALUES(stream),
                            form   = VALUES(form);

This will instead, update the records of the offending row to the values specified in the INSERT portion of the query.

References:

• https://dev.mysql.com/doc/refman/5.7/en/replace.html
• https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

---

Updated based on new intentions

In order to create a new record while leaving the previous row, you would need to first invalidate the UNIQUE constraint on the existing row, prior to using INSERT. There is currently no method to do this with MySQL in a single query.

Due to limitations on INSERT, the easiest way to accomplish what you need is to run an UPDATE query prior to the INSERT query.

UPDATE classes 
   SET teacher_id = NULL
   WHERE teacher_id = '7';
INSERT INTO classes
   SET stream = 'Red', 
   form = '1',
   teacher_id = '7';

This will first check for a conflicting teacher_id record and set it to null. If no conflicting record exists, no update will occur. The following INSERT would then create the desired record, without a conflict occurring.