[thelist] [SQL] deleting records from associated tables
Sean German
ethanol at mathlab.sunysb.edu
Thu May 23 01:47:01 CDT 2002
Howdy,
I'm spacing on something I think should be straightforward, but I don't have
access to the db to check my thinking. I need to delete records from
several tables all associated with one central table. Keys for the records
to be deleted are in a separate table.
In this case the central table is tblTracking, with the records to be
deleted in tblTrackingDeletions. Other tables are joined with tblTracking
by foreign key. 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?
Does this look right?
Thanks,
Sean G.
DELETE
FROM tblTracking RIGHT OUTER JOIN
tblWorkOrder on (tblTracking.WorkOrderNum = tblWorkOrder.WorkOrderNum) RIGHT
OUTER JOIN
tblInvoice on (tblTracking.InvoiceNum = tblInvoice.InvoiceNum) RIGHT OUTER
JOIN
tblData on (tblTracking.DataKey = tblData.DataKey) INNER JOIN
tblTrackingDeletions on (tblTracking.TrackingKey =
tblTrackingDeletions.TrackingKey)
tblTacking
----------
TrackingKey (pk)
WorkOrderNum (fk)
InvoiceNum (fk)
DataKey (fk)
tblWorkOrder
------------
WorkOrderNum (pk)
WorkOrderReference
tblInvoice
----------
InvoiceNum (pk)
RecdDate
Status
tblData
-------
DataKey (pk)
Comments
tblTrackingDeletions
-----------------
TrackingKey
More information about the thelist
mailing list