[thelist] [SQL] deleting records from associated tables
rudy
r937 at interlog.com
Thu May 23 07:11:00 CDT 2002
> the central table is tblTracking, with the
> records to be deleted in tblTrackingDeletions.
hi sean
that's good, and shows the designer was thinking
> Other tables are joined with tblTracking by foreign key.
so, um, do you have relational integrity in your database?
you didn't say which database you're running
> Since I want existing records to be deleted with
> if there aren't coorespoding records in all of the
> associated tables (tblWorkOrder, tblInvoice, tblData),
> I want to use OUTER JOINs, right?
this sounds like a job for ON DELETE RESTRICT
RI to the Rescue!!
http://www.evolt.org/article/RI/18/13276/index.html
if you cannot declare RI, you'll probably have to do something like this --
delete from tblTracking
where TrackingKey in
(select TrackingKey
from tblTrackingDeletions)
and WorkOrderNum NOT in
(select WorkOrderNum
from tblWorkOrder)
and InvoiceNum NOT in
(select InvoiceNum
from tblInvoice)
and DataKey NOT in
(select DataKey
from tblData)
rudy
More information about the thelist
mailing list