Aaron Aaron - 1 year ago 64
MySQL Question

PHP PDO execute 2 queries in the event of a duplicate key

I am currently using this query

insert into races(race_city_id, race_date, racers, division, elimination)
values (:race_city_id, :race_date, :racers, :division, :elimination)
on duplicate key update
race_city_id = values(race_city_id),
race_date = values(race_date),
racers = values(racers),
division = values(division),
elimination = values(elimination);

with a unique key on race_date, division, and elimination. I am currently using
on duplicate key
, but on a duplicate key found, I need to either delete and insert a new row into races (2 queries) or somehow regenerate the id field of the row and update some fields in it. I am using InnoDB and I have read that when you update a row the primary key would be regenerated, but mine is not. The id is set to auto increment, so I'm not sure if there is a way to regenerate the key.

The query updates the row properly, but I just need the key to be regenerated too.

Answer Source

Change the query to:

replace into races(
) values (

As replace into works exactly like insert except that if an old row in the table has the same value as a new row for a primary key or a unique index, the old row is deleted before the new row is inserted, generating a new id.