Node Mysql On Duplicate Key Update only updates a unique row

Question

I am using MySQL 5.7 with Node JS 6.11.0 and trying to update the UNIQUEMySQL column whenever I insert a conflicting row. However, when I try to insert a conflicting record, only the existing record is updated to NULLand not insert. 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 need to execute the query twice for the row to be inserted; the first time the conflicting column is zero, then when I run the same query again, the row is inserted because there are no conflicts.

I took the created SQL and directly run it from the MySql console, and I still have to run the query twice for the new row that I need to insert. I don’t understand why this is done.

Sql

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

create SQL

| 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 |`

MySQL ?

Answer 1

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

MySQL, ( ), teacher_id null .

.

REPLACE.

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

UNIQUE Primary Key . , . ( ) , . , INSERT.

VALUES UPDATE.

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

, INSERT .

:

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

---

, , UNIQUE , INSERT. MySQL .

- INSERT, , , - UPDATE INSERT.

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

teacher_id null. , . INSERT .

Answer 2

,

INSERT INTO table1 SELECT 'Red',1,7 FROM table2 where teacher_id IS NULL;

else, .

update table1 SET stream = 'Red', form = '1', teacher_id = '7' where teacher_id IS NULL;

: NULL = NULL - NULL. , IF (NULL = NULL) , NULL FALSE NULL. , NULL ( IS NULL()). , .