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
I have fixed the issue by using join. Please find it below,
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.
Key for table students, your previous version is better key(
name
). thegroup
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.