Jamal Kendrick Jamal Kendrick - 1 year ago 76
SQL Question

Duplicate PRIMARY key error when writing UPDATE statement

I am trying to update two tables using an UPDATE statement. My goal is:
This script will delete the reservation for Lamar Lincoln from trip #562,
and it will add a new reservation for Lamar Lincoln to trip #564.
As an alternative, you may accomplish the same end with an update transaction.
However, I get an error stating the I have duplicate keys, 6-562? Both trip_number and guest_id are PRIMARY keys, but Lamar (guest_id = 9) is on two trips, 562, 579.
This is my DB design

use www;
UPDATE reservation
SET trip_number=564,
WHERE trip_number=562, guest_id=9;

Answer Source

Try the syntax suggested by Galz. If you get the same error, then there must be a record in reservation with trip_number 562 and guest_id 9.

Best practice: do not change primary key value after record creation, even for compound primary keys in linking tables like this one.

DELETE FROM reservation WHERE guest_id = 9 AND trip_number = 562;
INSERT INTO reservation (guest_id, trip_number) VALUES (9, 564);