delete records from more than one table?

[estafford]

We are using archiving software that stores info in an access database.
The database contains three tables that I'll call tb1, tb2, and tb3.

All tables have a field called "id"
tb2 and tb3 might have matching "id" values to tb1.

What I want to do is delete data from tb1 and if tb2 and tb3 have an "id" value found in tb1, then delete those as well.

But I want to do this by checking a value of a field in tb1 using "LIKE".

For example: If field "title" starts with "[notice]", then I want to delete the record and also any record in tb2 and tb3 that has the same "id" field value as tb1.

The query using "like" could potentially return hundreds of records. Is there an easy way to do this in a single query or batch?

 

[Chopstik]

I suspect there are a couple of different ways you could approach this, but first, I would not delete anything using "LIKE" as the operator for your criteria. As you noted, you could easily return/delete records that you do not intend to otherwise delete. You should delete only the records you want, preferably using "=".

Second, you could permit cascading deletes in your database, though I would not recommend this as it could have other than intended consequences.

Third, and IMO the most preferred, would be to do a transaction when doing your delete process. In SQL Server, you would encapsulate your DELETE statements with BEGIN TRAN and END TRAN, but you would need to see the correct syntax for the DB you are using. If using SQL Server, you can find more information in BOL.

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous

 

[DreXor]

there was a recent post about cascading deletes/updates dealt with adding some relationships between the ID's and applying update/delete across the relationships, i cant remember the thread description, but tarwn posted most of the info in it. i've tried searching for the thread but i cant remember all the stuff in it to be able to pinpoint it via search.. maybe someone else will remember better and post you a link.

DreX
aKa - Robert