Deleting from Multiple Tables

[LittleSmudge]

I have two tables that are Import Quarantine tables populated from CSV files.

The tables are linked in a one to many relationship - but as I can't trust the validity of the data I cannot guarantee the referential integrity.

tblImportTest
TestId
field1
field2
etc.

tblImportContent
TestRef ForeignKey to tblImportTest.TestId
field1
field2
etc.

I want to create a patameter query that will delete any records for a matchine TestId from both tables ( IF they exist ) bit remember that I can't guarantee record will exist in either table.


I've tried

DELETE tblImportTest.*, tblImportContent.*
FROM tblImportTest, tblImportContent
WHERE tblImportTest.TestId = [Test Instance ID To Be DELETED] 
AND tblImportContent.TestRef = [Test Instance ID To Be DELETED];

When I run this I get
"Could not delete from specified tables."


Is there any way of combining a Delete from the two tables into one query ?


G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[PHV]

I'm afraid you've to play with 2 delete queries:
1) delete the many-side records
2) delete in the main table

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LittleSmudge]

Thanks PHV I'm handing this over to others as of tomorrow ( 8th November ) because my contract has come to an end and I wanted to make it as simple and idiot proof as possible. With any two step process their's always the possibility of missing the second step.

But if it can't be done in one then two it has to be.


Thanks again for the confirmation.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.