Lost part of a Primary Key

owain · June 13th, 2003, 09:22 AM

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:

Code:

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.

Regards
Owain Williams

Jeff Mason · June 13th, 2003, 09:36 AM

You can't use a table name alias in an UPDATE statement. Actually, you can, but as you've seen, nothing happens. This is because in effect a table alias materializes a duplicate table with the alias name (it doesn't really, but the effect is as if it did), so what you are updating is actually this 'duplicate' table, and that disappears when the statement finishes executing.

Do two things:

1. Remove the alias name from the UPDATE statement and refer to the actual table name.
2. Go add foreign key constraints right now so this doesn't happen again.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

owain · June 13th, 2003, 10:20 AM

It did not work :(

I have to do this in MS Access, so that may cause a problem. Access doesn't understand Image.AppointmentNo (or i.AppointmentNo). I have also tried the following query, however Access says 'Operation must use an updateable query':

Code:

UPDATE Appointment
SET Appointment.AppointmentNo = (SELECT DISTINCT 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 Appointment.CustomerID = i.CustomerID)
WHERE Appointment.AppointmentNo Is Null

I have also tried this without using alias's. Any other ideas?

Regards
Owain Williams

Jeff Mason · June 13th, 2003, 10:27 AM

And people wonder why I dislike Access...

The query you posted should work, or at least it looks valid.

What is the structure of both of the tables? Please include column and key definitions, please.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

owain · June 13th, 2003, 10:53 AM

Oh dear, I have just found out that the Appointment table and the Image table both don't have a primary key (they should). I do not know what or who is responsible for removing the primary key's. Both the tables have lots of columns, so I shall only list the most relevant ones. Here goes:

[u]Appointment Table</u>
RecNo - int
CustomerID - varchar(20)
AppointmentNo - smallint
AppointmentDesc - varchar(30)
AppointmentDate - date
AppointmentTime - date
AppointmentNote - Memo (BLOB)
...

[u]Image Table</u>
RecNo - AutoNumber (IDENTITY)
CustomerID - varchar(20)
AppointmentNo - smallint
ImageNo - smallint
ImageStorage - OLE Object (BLOB)
ImageNote - Memo (BLOB)
...

Regards
Owain Williams

Jeff Mason · June 13th, 2003, 11:12 AM

Yup, as I feared.

Access requires that there be a primary key in order to UPDATE (or DELETE too, I think). I vaguely recall issues with DISTINCT and primary keys as well, but I'm not sure of that.

All your tables really should have a primary key defined...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

owain · June 15th, 2003, 01:19 PM

Thanks for all your help. I guess I will have to do it pragmatically after all.

There should be a primary key in both of the tables, I don't know why or how it was removed. It certainly is there on the database we ship to our customers.

Ah well, just another bug that will have to be exterminated.

Regards
Owain Williams

Jeff Mason · June 15th, 2003, 01:29 PM

Good luck finding the bug, if there really is one.

Chances are that it was actually some person who physically removed the primary key indicator in table design. It's not all that hard to do - only a couple of mouse clicks.

Doing it in software is a bit more difficult...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com