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
WHERE trip_number=562, guest_id=9;
Try the syntax suggested by Galz. If you get the same error, then there must be a record in
trip_number 562 and
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);