Query to delete across tables

[Gauchey]

I think this is a pretty basic question. I am trying to delete records in two different tables. I get the error message:

Specify the table containing the records you want to delete.

The table MSShipment will be the reference source for the records I would like to delete across both tables.

DELETE MSShipment.BoxNumber AS MSShipment_BoxNumber, MedicalSort.BoxNumber AS MedicalSort_BoxNumber
FROM MSShipment
INNERJOIN MedicalSort
ON MSShipment.[BoxNumber]= MedicalSort.[BoxNumber];

[CJ_London]

you can only delete from one table at a time. this would normally be managed by setting your relationship between the two tables as maintaining referential integrity and cascade delete records. Then deleting the one side of the relationship (MSShipment?) will automatically delete the many side of the relationship. So your query would be

DELETE * FROM MSShipment

Note you can only set this relationship if it already has integrity - if you have records in MedicalSort that don't relate back to MSShipment then you won't be able to set the relationship anyway.

To find out if you have unrelated records in MedicalSort then run this query

SELECTMedicalSort.*
FROM MedicalSort 
LEFT JOIN MSShipment 
ONMedicalSort.[BoxNumber]=MSShipment.[BoxNumber]
WHERE MSShipment.[BoxNumber] is null;

[Gauchey]

Thank you for responding so quickly. I am not able to set integrity for these two tables at least as I understand it. There are records that will be in Medicalsort that will not exist in Msshipment. When I try to enforce that it tells me that there are records in one table that do not exist in the other. That being said, is there a way to delete the records from one table and use a second table as the reference for those that should be deleted? I have a relationship established just not referential integrity. Thanks for your help with this.

[June7]

Will have to delete the 'dependent' records first. Maybe like:

DELETE FROM Medicalsort WHERE [BoxNumber] IN (SELECT [BoxNumber] FROM Medicalsort INNER JOIN Msshipment ON Medicalsort.[BoxNumber] = Msshipment.[BoxNumber])

Since this is deleting complete records there is no need to explicitly list fields in the DELETE action.

[Gauchey]

June7 thank you so much!

[CJ_London]

I was going to suggest

DELETE * FROM Medicalsort WHERE [BoxNumber] IN (SELECT [BoxNumber] FROM Msshipment)

[June7]

Yeah, I guess that is all that is needed. Are all records also to be deleted from Msshipment? There will be orphan records left in Medicalsort?

Don't even need the * wildcard.

[Gauchey]

June7, I tested it and it works as needed. It is intentional to have orphan records. When we ship medical supplies it is not always possible to fit everything into the container and thus we need to know what we have left. Thanks again