[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