I have a table called Appointment which contains (among other things) a CustomerID (varchar) and an AppointmentNo (int). Unfortunately the software decided to steal a few of the AppointmentNo's so I have rows with a CustomerID and no AppointmentNo. Customers can have more than one appointment, so the AppointmentNo column is supposed to increase by one each time they have a new appointment.
Linked to this table I have an Image table which contains details of each picture that was taken at the corresponding appointment. This uses the columns CustomerID and AppointmentNo as foreign keys to the appointment table. Now I have images that have been orphaned from their appointment row's (yes, I know I should have used referential integrity and constraints but it is done now and I need a solution).
There is only one appointment for each customer that has had its AppointmentID removed, so I can use the rows in the Image table to identify the correct AppointmentNo in the appointment table. However how can I do this in SQL?
I have achieved some limited success using a correlated sub query, however I can not actually update the rows. Here is the SQL I am using:
UPDATE Appointment AS a
SET a.AppointmentNo = i.AppointmentNo
WHERE a.AppointmentNo Is Null AND EXISTS (SELECT i.AppointmentNo
FROM Image AS i
LEFT JOIN Appointment AS as
ON i.CustomerID = as.CustomerID
AND i.AppointmentNo = as.AppointmentNo
WHERE as.AppointmentNo Is Null
AND a.CustomerID = i.CustomerID)
However I can not use the i.AppointmentNo declared inside the sub query outside of the sub query. If I change it to a SELECT statement just for testing and use the columns CutomerID and AppointmentNo I get the correct rows, however I need to update these rows. Any suggestions would be greatly received.