on cascade delete - deleting from multiple tables

  • Thread starter Thread starter chino
  • Start date Start date
C

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
 
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
 
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?
 
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
 
Back
Top