Delete query

[Jack]

Hello,

I'm trying to delete records that have related data in 3 other tables.

i'm using Access 2007 with linked tables to the SQL 2000 database. I've
also tried running this in query analyzer.


When I run the select query it returns 12 records
:
SELECT DISTINCTROW dbo_Sessions.*, dbo_Clients.ClientNo, dbo_Sessions.TrxNo,
dbo_Therapists.TherapistID, dbo_Services.ServiceID, dbo_TrxMaster.TrxNo,
dbo_TrxMaster.TrxDate, dbo_TrxMaster.Amount
FROM (((dbo_Sessions LEFT JOIN dbo_Services ON dbo_Sessions.ServiceNo =
dbo_Services.ServiceNo) LEFT JOIN dbo_Clients ON dbo_Sessions.ClientNo =
dbo_Clients.ClientNo) LEFT JOIN dbo_TrxMaster ON dbo_Sessions.TrxNo =
dbo_TrxMaster.TrxNo) LEFT JOIN dbo_Therapists ON dbo_Sessions.TherapistNo =
dbo_Therapists.TherapistNo
WHERE (((dbo_Clients.ClientNo) Is Null));

When I run the delete query I recieve the message similar to microsoft
access can't delete 0 record in the delete query due to key violations and 0
record due to lock violations

DELETE DISTINCTROW dbo_Sessions.*, dbo_Clients.ClientNo, dbo_Sessions.TrxNo,
dbo_Therapists.TherapistID, dbo_Services.ServiceID, dbo_TrxMaster.TrxNo,
dbo_TrxMaster.TrxDate, dbo_TrxMaster.Amount
FROM (((dbo_Sessions LEFT JOIN dbo_Services ON dbo_Sessions.ServiceNo =
dbo_Services.ServiceNo) LEFT JOIN dbo_Clients ON dbo_Sessions.ClientNo =
dbo_Clients.ClientNo) LEFT JOIN dbo_TrxMaster ON dbo_Sessions.TrxNo =
dbo_TrxMaster.TrxNo) LEFT JOIN dbo_Therapists ON dbo_Sessions.TherapistNo =
dbo_Therapists.TherapistNo
WHERE (((dbo_Clients.ClientNo) Is Null));


What am I doing wrong??? This is driving me crazy.

 

[Jerry Whittle]

If the data was all in Access and if you wanted to delete all records under a
'parent' in the topmost table, cascade delete would work.

Your query is returning 12 records but there could be many, many more as you
are using a Distinctrow.

Delete queries definitely won't work with a Distinctrow clause or anything
but an INNER join. Therefore as you are deleting from three related tables,
you might not be able to do it all at once. You may need to delete the child
records first from each table. Next you can try to delete parent records;
however, it the relationships are set up properly, Access or SQL Server
should not allow this to happen if there is even one child record left.

You may need to write some of the joins as subqueries to get it to work. It
would look something like:

DELETE RC_Main.*
FROM RC_Main
WHERE RC_Main.ID In (select ID from [Pending Deletion IDs]);

 

[Ken Sheridan]

Its difficult to make out just what this is meant to do, but it looks to me
as if its intended to delete rows from the dbo_Sessions table which have no
matching rows in the dbo_Clients table. The other tables don't appear to
have a bearing on the criteria for deletion as far as I can see. If these
other tables include rows referencing the dbo_Sessions table, however, and
you want these to be deleted also from those tables, then enforcing cascade
deletes in the relationships would achieve this. Otherwise rows in
dbo_Sessions with matches in the other tables would not be deleted, assuming
that referential integrity is enforced.

If my understanding of what's intended is correct then the following should
do it:

DELETE *
FROM dbo_Sessions
WHERE NOT EXISTS
(SELECT *
FROM dbo_Clients
WHERE dbo_Clients.ClientNo = dbo_Sessions.ClientNo);

As always with set operations like this be sure to back up the tables until
you are absolutely sure that the right result is being achieved.

Ken Sheridan
Stafford, England