Conditional UPDATE MariaDB (MySQL)

Code:

UPDATE COMPANY SET id='21' 
WHERE id='20';

Error:

SQLException: Duplicate entry ’21’ for key ‘PRIMARY’

I want to UPDATE the primary key field in this case it’s called ‘id’ to another value but if the value exists already it throws the error above. How would I do a conditional UPDATE based on if the ‘id’ doesn’t exist in the COMPANY table already, to avoid throwing that error using MariaDB syntax.

NOTE: I am NOT talking about doing a conditional INSERT that uses “ON DUPLICATE KEY UPDATE” as shown below.

INSERT INTO COMPANY(id,first,last,age)
VALUES('1','Tim','Jones','70')
ON DUPLICATE KEY UPDATE id='1';

Answer

You can use UPDATE IGNORE:

UPDATE IGNORE COMPANY 
SET id='21' 
WHERE id = '20'

See a simplified demo.

Advertisement

Answer