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

Question

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.

You realize you're attaching the values on the insert with pdo, but not on the duplicate keys? on duplicate key update race_city_id = values(:race_city_id), etc... — Xorifelse
– Xorifelse, Commented Sep 6, 2016 at 1:21
@Xorifelse I'm pretty sure that using value(xxx) takes the value that would be inserted, and in this case it was from pdo. This is what I have used for 4 other queries that I have made. dev.mysql.com/doc/refman/5.7/en/… — Aaron
– Aaron, Commented Sep 6, 2016 at 1:24
After doing a little research it seems you are correct, however the query does what it reads, it updates the values and leaves the id alone. Perhaps using replace into races would resolve your situation (obviously removing the on duplicate key update part) — Xorifelse
– Xorifelse, Commented Sep 6, 2016 at 1:35
@Xorifelse Well is there a way to make it regenerate the id? I guess I could find the max of every id, but then the auto increment feature would be off after that, right? — Aaron
– Aaron, Commented Sep 6, 2016 at 1:38

Xorifelse · Accepted Answer · 2016-09-06 01:45:00Z

1

Change the query to:

replace into races(
  race_city_id, 
  race_date, 
  racers, 
  division, 
  elimination
) values (
  :race_city_id,
  :race_date,
  :racers,
  :division,
  :elimination
)

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.

answered Sep 6, 2016 at 1:45

Xorifelse

7,9411 gold badge29 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related