INSERT if not exists, UPDATE if exists, but only when certain condition is met

Question

I am not sure how to best solve this in the way I would like it to behave to my MYSQL database (via PHP).

Assuming we have a table with columns: id, name, city, status, last_modified, created_timestamp
id is the primary key.
created_timestmap has the attribute "ON UPDATE CURRENT_TIMESTAMP()"

I want to insert into this table 100 values with a INSERT statement. INSERT the row into the table when the record does not exist yet UPDATE the row with id, name, city, last_modified when the record already exist. leave created_timestamp and status untouched. And only update the row when status = <specific value>

I have tried INSERT ... ON DUPLICATE KEY UPDATE and INSERT IGNORE and REPLACE but they all are not meeting all requirements. This is what I tried:

INSERT INTO table_name (id, name, city, status, last_modified) VALUES <bunch of values> ON DUPLICATE KEY UPDATE id=VALUES(id), name=VALUES(name), city=VALUES(city), last_modified=CURRENT_TIMESTAMP()

inserts row correctly. updates when already exists.
I cannot give it the condition for status = <specific value>

INSERT IGNORE INTO table_name (id, name, city, status, last_modified) VALUES <bunch of values>

inserts row correctly. does not update existing records
I cannot give it the condition for status = <specific value>

REPLACE INTO table_name (id, name, city, status, last_modified) VALUES <bunch of values>

inserts row correctly
when already exists, it deletes and re-creates the row which messes with created_timestamp
I cannot give it the condition for status = <specific value>

What construct is best helpful to address my requirements?

Check conditional update if it fits your need https://thewebfellas.com/blog/conditional-duplicate-key-updates-with-mysql/ — meridbt, Aug 08 '22 at 15:45
Is it the new value of `status` that should be checked, or the old value? — Barmar, Aug 08 '22 at 15:46

score 1 · Answer 1 · answered Aug 08 '22 at 15:50

You can use a IF() expressions in the ON DUPLICATE KEY UPDATE clause to make the updates conditional.

INSERT INTO table_name (id, name, city, status, last_modified) 
VALUES (<bunch of values>) 
ON DUPLICATE KEY UPDATE 
    id=IF(status = @value, VALUES(id), id), 
    name=IF(status = @value, VALUES(name), name), 
    city=IF(status = @value, VALUES(city), city),
    last_modified=IF(status = @value, CURRENT_TIMESTAMP(), last_modified)

@value represents the <specific value> you want to test.

INSERT if not exists, UPDATE if exists, but only when certain condition is met

1 Answers1