CODEWITHSUNDEEP
mysqlreplaceinsertsql-updateunique-key
user2509541
user2509541

Reputation: 268

MYSQL to UPDATE table if row with 2 specific columns exist or INSERT new row if it does not exist

I have a MYSQL table that looks as follows:

id    id_jugador    id_partido    team1     team2
1        2              1           5         2
2        2              2           1         1
3        1              2           0         0

I need to create a query to either INSERT new rows in the table or UPDATE the table. The condition is based on id_jugador and id_partido, meaning that if I wanted to insert id_jugador = 2 and id_partido = 1, then it should just UPDATE the existing row with the new team1 and team2 values I am sending. And dont duplicate the row.

However, if I have an entry id_jugador=2 and id_partido=3, since this combination does not exist yet, it should add the new row.

I read about the REPLACE INTO but it seems to be unable to check combination of UNIQUE KEYS.

Upvotes: 0

Views: 1187

Answers (1)

spencer7593
spencer7593

Reputation: 108400

If you have a UNIQUE KEY defined on the two columns (id_jugador, id_partido), then you can use:

INSERT ... ON DUPLICATE KEY ...

e.g.

INSERT INTO mytable (id_jugador, id_partido, team1, team2) 
VALUES (?, ?, ?, ?)
ON DUPLICATE KEY
UPDATE team1 = VALUES(team1)
     , team2 = VALUES(team2)

(Obviously, I'm assuming id is an AUTO_INCREMENT PRIMARY KEY, or there's a BEFORE INSERT trigger that generates a value for id when it's not provided.)

MySQL will attempt the INSERT (using up an auto_increment id value). If the INSERT succeeds, MySQL is done, it's just a regular insert. But if the INSERT throws a DUPLICATE KEY exception, then MySQL will perform an update action, equivalent to:

UPDATE mytable SET team1 = ?, team2 = ? WHERE id_jugador = ? AND id_partido = ?

Note that there can be multiple UNIQUE KEY constraints defined on a table. For the predicates of the UPDATE action, MySQL will use the columns/values of whichever unique (or primary) key is identified in the DUPLICATE KEY exception.

Also note that MySQL does actually attempt the INSERT, so MySQL does use up an AUTO_INCREMENT value, even when the INSERT throws a DUPLICATE KEY exception.

I normally avoid a REPLACE, because I usually have a lot of foreign keys, and I don't want a DELETE action. The DELETE action performed by REPLACE is a "real" DELETE; if there are foreign key references, the DELETE rule associated with the foreign key will be obeyed... the delete will fail if the DELETE rule is RESTRICT or NO ACTION and referencing rows exist, or if the DELETE rule is CASCADE or SET NULL, the referencing rows will be deleted or updated. I also believe any BEFORE/AFTER DELETE triggers will also be fired.

Apart from those options, you'd have to run two separate statements.

When I've needed to avoid an INSERT without having a UNIQUE KEY defined, I will typically do something like:

INSERT INTO mytable (id_jugador, id_partido, team1, team2) 
SELECT i.*
  FROM (SELECT ? AS id_jugador, ? AS id_partido, ? AS team1, ? AS team2) i
  LEFT
  JOIN mytable s
    ON s.id_jugador <=> i.id_jugador
   AND s.id_partido <=> i.id_partido
 WHERE s.id IS NOT NULL

After the statement executes, test the number of affected rows. If it's zero, we know that the row was not inserted, so we can proceed with an UPDATE.

Upvotes: 1

Related Questions