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
Last activity: Jan 17, 2025, 08:00 PM