By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE employee_attendance(
DbKey INT PRIMARY KEY AUTO_INCREMENT,
EmployeeDbKey INT,
date DATE,
InTime TIME,
OutTime TIME);
INSERT INTO employee_attendance(EmployeeDbKey, date, InTime, OutTime) VALUES
(2, '2022-01-01', '09:01','17:30'),
(2, '2022-01-02', '08:30','17:30'),
(2, '2022-01-03', '08:41','17:30'),
(2, '2022-01-04', '08:01','17:30'),
(2, '2022-01-04', '08:01','17:30'),
(2, '2022-01-04', '08:01','17:30'),
(3, '2022-01-01', '07:01','16:30'),
(3, '2022-01-01', '07:01','16:30');
Records: 8 Duplicates: 0 Warnings: 0
SELECT * FROM employee_attendance;
| DbKey | EmployeeDbKey | date | InTime | OutTime |
|---|---|---|---|---|
| 1 | 2 | 2022-01-01 | 09:01:00 | 17:30:00 |
| 2 | 2 | 2022-01-02 | 08:30:00 | 17:30:00 |
| 3 | 2 | 2022-01-03 | 08:41:00 | 17:30:00 |
| 4 | 2 | 2022-01-04 | 08:01:00 | 17:30:00 |
| 5 | 2 | 2022-01-04 | 08:01:00 | 17:30:00 |
| 6 | 2 | 2022-01-04 | 08:01:00 | 17:30:00 |
| 7 | 3 | 2022-01-01 | 07:01:00 | 16:30:00 |
| 8 | 3 | 2022-01-01 | 07:01:00 | 16:30:00 |
CREATE TABLE employee_attendance_new LIKE employee_attendance;
ALTER TABLE employee_attendance_new
ADD UNIQUE INDEX unq_idx(EmployeeDbKey, date, InTime, OutTime);
SHOW CREATE TABLE employee_attendance_new;
Records: 0 Duplicates: 0 Warnings: 0
| Table | Create Table |
|---|---|
| employee_attendance_new | CREATE TABLE `employee_attendance_new` ( `DbKey` int NOT NULL AUTO_INCREMENT, `EmployeeDbKey` int DEFAULT NULL, `date` date DEFAULT NULL, `InTime` time DEFAULT NULL, `OutTime` time DEFAULT NULL, PRIMARY KEY (`DbKey`), UNIQUE KEY `unq_idx` (`EmployeeDbKey`,`date`,`InTime`,`OutTime`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
INSERT IGNORE INTO employee_attendance_new
SELECT * FROM employee_attendance;
Records: 8 Duplicates: 3 Warnings: 3
SELECT * FROM employee_attendance_new;
| DbKey | EmployeeDbKey | date | InTime | OutTime |
|---|---|---|---|---|
| 1 | 2 | 2022-01-01 | 09:01:00 | 17:30:00 |
| 2 | 2 | 2022-01-02 | 08:30:00 | 17:30:00 |
| 3 | 2 | 2022-01-03 | 08:41:00 | 17:30:00 |
| 4 | 2 | 2022-01-04 | 08:01:00 | 17:30:00 |
| 7 | 3 | 2022-01-01 | 07:01:00 | 16:30:00 |