5

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?

Jakub Matczak
  • 15,341
  • 5
  • 46
  • 64
Dennis Wanyonyi
  • 368
  • 1
  • 5
  • 18

2 Answers2

9
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:

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.

Will B.
  • 17,883
  • 4
  • 67
  • 69
  • What I intended to achieve was to have the offending row updated first then a new row to be inserted. Is it possible to accomplish this? – Dennis Wanyonyi Aug 16 '17 at 08:44
  • So @fyrye that means I have to enable `multiple statements` in MySQL for the query you have suggested to work. – Dennis Wanyonyi Aug 17 '17 at 06:24
  • @DennisWanyonyi You could execute the queries separately. There are alternatives to process the desired functionality in the same manner, such as a stored procedure to execute the queries or the use of triggers. But there's no single query operation to perform an `INSERT` and maintain the original row data, as you need to tell MySQL what to do with the conflicting row and you don't want to lose the association of classes to students on the class id. – Will B. Aug 17 '17 at 14:35
  • how to pass a value in case of updating? – baldraider Nov 06 '18 at 06:29
  • @sasuke please post your question on StackOverflow, so that we can get some more information to assist you better. – Will B. Nov 06 '18 at 06:33
2

If you want to insert data based on condition try this

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

else if you want to update based on trying this.

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

Note: NULL = NULL is not really FALSE - it's NULL again. But it's not TRUE either, so IF(NULL = NULL) won't execute For example, NULL will test FALSE even against NULL itself. So, working with NULL is only done with the aforementioned functions (and IS NULL()). Your query could be rewritten as above.

HariKishore
  • 149
  • 8