Update scenario is not working properly when using CASEs or IF statement ON DUPLICATE KEY UPDATE section in MySQL

Question

I need to do bulk insert and update on MySQL so I used the ON DUPLICATE KEY UPDATE. I need to ignore the updated by and updated date fields as existing old data if there are no changes in those record's names or statuses.

Please find the code below,

INSERT INTO student 
        (group, name, status, created_by, created_date, updated_by, updated_date)
        SELECT 
        group, name, status, 
        'system',  UTC_TIMESTAMP(),
        'system', UTC_TIMESTAMP()
        FROM old_students  
        ON DUPLICATE KEY 
        UPDATE 
            group = VALUES(group), 
            status = VALUES(status), 
            name= VALUES(name), 
            updated_by = 
                        (CASE
                        WHEN (VALUES(name) <> students.name)
                        THEN 'system'
                        ELSE students.updated_by
                        End),
            updated_date = 
                        (CASE
                        WHEN (VALUES(name) <> students.name)
                        THEN UTC_TIMESTAMP()
                        ELSE students.updated_date
                        End);

Please find the table definition below,

    CREATE TABLE `old_students` (
      `id` int NOT NULL AUTO_INCREMENT,
      `group` varchar(40) Not NULL,
      `name` varchar(40) DEFAULT NULL,
      `status` varchar(10) DEFAULT NULL,
      `created_by` varchar(10) NOT NULL,
      `created_date` datetime NOT NULL,
      PRIMARY KEY (`id`)
    ) 
    
    
    CREATE TABLE `students` (
      `group` varchar(40) Not NULL,
      `name` varchar(40) DEFAULT NULL,
      `status` varchar(10) DEFAULT NULL,
      `created_by` varchar(10) DEFAULT NULL,
      `created_date` datetime DEFAULT NULL,
      `updated_by` varchar(10) DEFAULT NULL,
      `updated_date` datetime DEFAULT NULL,
       primary key `group` (`group`)
    )

the true block is not executing in updated_by and updated_date columns.

Also I tried the IF statement too, but not working.

updated_by = IF((VALUES(name) <> students.name),  'system', students.updated_by)

The updated_by and updated_date need to be updated when a new record has different values compared with the existing record value. Otherwise, it should be an older existing record value.

Could anyone help to fix the issue?

Thanks in advance.

Answer 1

According to your request, if they names in students and old_studetns do not match, a new row will be inserted into the table.
In other case, when names in students and old_studetns is matched you do ON DUPLICATE key UPDATE.
As far as I can understand your task, in this case you can check the status change and/or the date of the record update. If there are changes, add them to the students table.

INSERT INTO student (name, status, created_by, created_date
                  , updated_by, updated_date)
SELECT  name, status, 
        'system' as created_by,  UTC_TIMESTAMP() as created_date,
        'system' as updated_by, UTC_TIMESTAMP() as updated_date
FROM old_students  
ON DUPLICATE KEY 
    UPDATE 
       status = VALUES(status), 
       name= VALUES(name), 
       updated_by = 
                   (CASE WHEN (VALUES(status) <> students.status)
                       THEN old_students.created_by
                    ELSE students.updated_by
                    END),
       updated_date = 
                   (CASE WHEN (VALUES(status) <> students.status)
                        THEN UTC_TIMESTAMP() -- old_students.created_date
                    ELSE students.updated_date
                    END)
;

Key for table students, your previous version is better key(name). the group will not give you anything, it will only confuse you.

P.S. Column names, sach as name,group - which match the reserved words of the language, is a bad practice.

Answer 2

I have fixed the issue by using join. Please find it below,

INSERT INTO students 
    (`group`, name, status, created_by, created_date, updated_by, updated_date)
SELECT 
    os.`group`, os.name, os.status, 
    'system', UTC_TIMESTAMP(), 
    CASE 
        WHEN os.name <> s.name THEN 'system'
        ELSE s.updated_by
    END,
    CASE 
        WHEN os.name <> s.name THEN UTC_TIMESTAMP()
        ELSE s.updated_date
    END
FROM 
    old_students os
LEFT JOIN 
    students s ON os.`group` = s.`group`

ON DUPLICATE KEY UPDATE 
    students.name = VALUES(name),
    students.status = VALUES(status),
    students.updated_by = VALUES(updated_by),
    students.updated_date = VALUES(updated_date)