Insert or Update MySQL

Question

I have a table

ID  CHIP_ID STATUS MOD_STATUS_DATE_TIME
1      123    w    2024-02-07 11:20:06
2      456    i    2024-02-07 11:21:00

I need insert or update this table in the following way:

1. If we have no row with the new chip_id we will Insert chip_id, status, now()
2. If we have a row with the chip_id AND new status is NOT the same as In the Latest (MOD_STATUS_DATE_TIME) we will Insert chip_id, status, now()
3. If we have a row with the chip_id AND new status is the same as In the Latest (MOD_STATUS_DATE_TIME) we will skip and do nothing.

Could we do this with the MySQL query? Thanks!

I did the same with two separate queries and some code but it would be perfect if I could do it with MySQL only.

Answer 1

Use trigger. Insert data with INSERT IGNORE.

CREATE TABLE test (
  id INT PRIMARY KEY,
  chip_id INT,
  status CHAR(1),
  mod_status_date_time DATETIME
  );
INSERT INTO test VALUES
(1,      123,    'w',    '2024-02-07 11:20:06'),
(2,      456,    'i',    '2024-02-07 11:21:00');

CREATE TRIGGER tr_bi_test
BEFORE INSERT ON test
FOR EACH ROW
BEGIN
    IF NEW.status = ( SELECT status 
                      FROM test 
                      WHERE chip_id = NEW.chip_id 
                      ORDER BY mod_status_date_time DESC LIMIT 1
                      ) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Last ''status'' for this ''chip_id'' is the same, insertion rejected.',
                MYSQL_ERRNO = 1062;
    ELSE
        SET NEW.mod_status_date_time = CURRENT_TIMESTAMP;
    END IF;
END

-- If we have no row with the new chip_id 
-- we will Insert chip_id, status, now()
INSERT IGNORE INTO test VALUES
(3,      789,    'w',    '2024-02-07 12:20:06');

-- If we have a row with the chip_id AND new status 
-- is NOT the same as In the Latest (MOD_STATUS_DATE_TIME) 
-- we will Insert chip_id, status, now()
INSERT IGNORE INTO test VALUES
(4,      123,    'x',    '2024-02-07 13:20:06');

-- If we have a row with the chip_id AND new status
-- is the same as In the Latest (MOD_STATUS_DATE_TIME)
-- we will skip and do nothing
INSERT IGNORE INTO test VALUES
(5,      123,    'x',    '2024-02-07 14:20:06');

Last 'status' for this 'chip_id' is the same, insertion rejected.

SHOW WARNINGS;

Level	Code	Message
Error	1062	Last 'status' for this 'chip_id' is the same, insertion rejected.

SELECT * FROM test;

id	chip_id	status	mod_status_date_time
1	123	w	2024-02-07 11:20:06
2	456	i	2024-02-07 11:21:00
3	789	w	2024-02-07 13:38:51
4	123	x	2024-02-07 13:38:51

fiddle