skip to Main Content

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.

2

Answers


  1. Chosen as BEST ANSWER

    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)
    

  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search