Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 13th, 2003, 09:22 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default Lost part of a Primary Key - Oops!

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
__________________
Regards
Owain Williams
 
Old June 13th, 2003, 09:36 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old June 13th, 2003, 10:20 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old June 13th, 2003, 10:27 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old June 13th, 2003, 10:53 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old June 13th, 2003, 11:12 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old June 15th, 2003, 01:19 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old June 15th, 2003, 01:29 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Foreign key not updating with Primary key xavier1945 BOOK: Access 2003 VBA Programmer's Reference 2 July 4th, 2007 09:48 PM
Primary key cf2006 BOOK: Beginning Visual Basic 2005 Databases ISBN: 978-0-7645-8894-5 1 July 31st, 2006 07:21 PM
FOREIGN KEY and PRIMARY KEY Constraints junemo Oracle 10 June 15th, 2004 01:00 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.