Sample Header Ad - 728x90

MySQL: how to insert only if update fails

0 votes
1 answer
607 views
There is a [similar question](https://stackoverflow.com/questions/52283031/running-a-mysql-insert-when-update-fails) , but my requirement is **different**. The table schema is:
CREATE TABLE device (
  id int NOT NULL,
  device_type varchar(64) NOT NULL,
  serial varchar(128) NOT NULL,
  revoked bigint NOT NULL DEFAULT '0',
  created datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id,serial,revoked)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_unicode_ci
I want to insert into the table with a pair of (id, serial), if **neither** of id or serial appears in the table. So, I think I might do this:
UPDATE device SET updated='' WHERE id='' OR 
serial='' ON UPDATE FAIL INSERT (id,serial,...) VALUES (...)
The **ON UPDATE FAIL...** part is hypothetical. I know that MySQL does not have this syntax. So I want a **transactional** way to accomplish this. Note that the PRIMARY KEY cannot be modified, although adding other indexes is allowed, without change business logic, of course.
Asked by xrfang (143 rep)
Oct 13, 2022, 06:16 AM
Last activity: Jan 17, 2025, 08:00 PM