CODEWITHSUNDEEP
mysqlsqljdbcmariadb
calculatedplays
calculatedplays

Reputation: 17

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';

Upvotes: 1

Views: 1141

Answers (2)

forpas
forpas

Reputation: 164089

You can use UPDATE IGNORE:

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

See a simplified demo.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You can count the number of values already in the table:

UPDATE COMPANY C CROSS JOIN
       (SELECT COUNT(*) as cnt
        FROM COMPANY
        WHERE id = 21
       ) CC
    SET c.id = 21
    WHERE id = 20 AND cnt = 0;

Note: In most databases, you would use NOT EXISTS in the WHERE clause, but MySQL/MariaDB doesn't support references to the table being updated.

Upvotes: 0

Related Questions