on cascade delete - deleting from multiple tables

[chino]

Hi

I have two tables linking through a unique identifier
(id).
info and pinfo. I want to delete certain records from
both where info.date < 01/01/2003 and info.type = "Vr1"
Pinfo contains information relating to the records i wish
to delete, which means i need to delete from there too.
I tried the following sql:

delete * from pinfo, info
where pinfo.id = info.id
and info.date < 01/01/2003
and info.type = "Vr1"

I know now that this does not work and i may need to do a
cascade delete, can anyone help with this?

Thanks

 

[Gerald Stanley]

Go to Tool->Relationships and define the relationship
between info and pinfo ensuring that you check the boxes
'Enforce Referential Integrity' and 'Cascade Delete Related
Records'.

Then change the delete SQL to
delete * from info
where info.date < #01/01/2003#
and info.type = "Vr1"

When you run the delete SQL as a query, you will be told
how many rows will be deleted from info. But when you go
into table pinfo, you should see that the related rows have
also been deleted.

Hope This Helps
Gerald Stanley MCSD

 

[Guest]

thanks for that, thats exactly what i needed
just as a side note, is there a way to do this through
just sql, not on access?

 

[Gerald Stanley]

Access does not support deletions from multiple tables so
you would have to do this in two statements e.g.

DELETE FROM pinfo
WHERE infoId IN (SELECT infoId FROM info WHERE info.date <
#01/01/2003# AND info.type = "Vr1")

then the delete from the info table.

Hope This Helps
Gerald Stanley MCSD