Archive/Delete and Referential Integrity

[MTBChik]

I am trying to set up an archive database. I have the export SQL statements working fine. The client then wants us to delete the records from 3 related tables where the deletion criteria is listed in one table.

ex. The deletion criteria is the paid date in the project table.

The task table uses the project id as a foreign key

The assignment table uses the task id as a foreign key but has no direct relationship to the project except through the task table.

Is it possible to use table aliases to specify when to delete a record? I have tried and keep getting errors.

Ex. (This was one of about 100 trial and error attempts)

DELETE FROM assignment a
AND DELETE FROM userid.project_task pt
AND DELETE FROM userid.project p
WHERE p.date_paid IS NOT NULL

Any help is appreciated.

 

[tlbroadbent]

You can't use AND between DELETE statements as in your query. I would delete rows from each table separately, starting with dependent tables. Enclose the deletes in a TRANSACTION to insure ROLLBACK if one of the delete statements fails.

BEGIN Transaction

-- First, delete assignment table rows
DELETE Assignement
FROM assignment a
INNER JOIN project_task pt
ON a.taskid=pt.taskid
INNER JOIN project p
ON pt.projectID=p.projectID
WHERE p.date_paid IS NOT NULL

-- Then delete project_task table rows
DELETE project_task
FROM project_task pt
INNER JOIN project p
ON pt.projectID=p.projectID
WHERE p.date_paid IS NOT NULL

-- Finally, delete project table rows
DELETE project
WHERE p.date_paid IS NOT NULL

COMMIT Transaction Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm