August 22, 2013 at 9:52 am
I have a Project master table (tblProjects). Each project has zero or more Work Orders in the work order table (tblWorkOrders) (FK). Each Work Order has zero or more timesheet entries in the Timesheet (tblSA) table.
I need to write a (probably stored procedure) that, for a given ProjectID (PK), there are no Timesheet entries, then
Delete all Work Orders for that ProjectID (FK on tblWorkOrders.)
None of the FKs are set to cascading delete, all are set to Referential Integrity.
What's the strategy for doing this?
Jim
August 22, 2013 at 9:57 am
JimS-Indy (8/22/2013)
I have a Project master table (tblProjects). Each project has zero or more Work Orders in the work order table (tblWorkOrders) (FK). Each Work Order has zero or more timesheet entries in the Timesheet (tblSA) table.I need to write a (probably stored procedure) that, for a given ProjectID (PK), there are no Timesheet entries, then
Delete all Work Orders for that ProjectID (FK on tblWorkOrders.)
None of the FKs are set to cascading delete, all are set to Referential Integrity.
What's the strategy for doing this?
NOT TESTED - USE AT OWN RISK!!!
delete workorders
from workorders wo
where wo.projectid = @projectid
and not exists (select * from timesheets t where wo.projectid = t.projectid)
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 3, 2013 at 8:45 am
OK, that's exactly what I wanted. Now, how to streamline this SQL (note the redundant select statements...) Using Project ID 0355 for testing...
begin transaction
delete tblWorkOrders
from tblWorkOrders wo
where wo.projectid=03555
and not exists (select * from tblStaffAugTrans SA where SA.WorkOrderID in (select ID from tblWorkOrders where tblWorkOrders.ProjectID=03555))
and not exists (select * from tblBoM BoM where BoM.BoMWOID in (select ID from tblWorkOrders where tblWorkOrders.ProjectID=03555))
and not exists (select * from tblELaborSpread sp where sp.WorkOrderID in (select ID from tblWorkOrders where tblWorkOrders.ProjectID=03555))
rollback
Jim
September 3, 2013 at 8:49 am
JimS-Indy (9/3/2013)
OK, that's exactly what I wanted. Now, how to streamline this SQL (note the redundant select statements...) Using Project ID 0355 for testing...
begin transaction
delete tblWorkOrders
from tblWorkOrders wo
where wo.projectid=03555
and not exists (select * from tblStaffAugTrans SA where SA.WorkOrderID in (select ID from tblWorkOrders where tblWorkOrders.ProjectID=03555))
and not exists (select * from tblBoM BoM where BoM.BoMWOID in (select ID from tblWorkOrders where tblWorkOrders.ProjectID=03555))
and not exists (select * from tblELaborSpread sp where sp.WorkOrderID in (select ID from tblWorkOrders where tblWorkOrders.ProjectID=03555))
rollback
I have modified this SP http://www.sqlteam.com/article/performing-a-cascade-delete-in-sql-server-7 to suit my needs and it works well
HTH
gsc_dba
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply