Deleting rows from multiple tables that share a FK

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Deleting rows from multiple tables that share a FK

Author

Topic

JMMILLS
Starting Member

2 Posts

Posted - 2009-09-01 : 12:47:35

I am trying re-learn SQL back on my home and hear is my issue.

I have a table called tblTrips whose ID is the primary key of that table. It is also the FK of tbltripsscheduled, table. It is also the FK of tbltripsscheduled, tbltripstemplate and tbltripsactual. I want to be able to delete all of the data from 06/01/2008 and backwards. I was thiking I needed to do something like this:
DELETE FROM tblTrips
FROM [ID].tblTrips AS ID
INNER JOIN TripID.tblTripsactual AS TripID
ON ID.ID = TripID.TRIPID
WHERE TRIPID.TRIPDATE > '20080601'

I then realized that I can't delete anything from trips since it's primary key is also the foreign key.

So then I just deleted the tbltripsscheduled, tbltripstemplate and tbltripsactual tables without realizing that I still need that data to delete and <> does not equal doesn't work for deleting

I need assistance making one scriot that will loop the three tables with the FK and the PK Table into deleting all the data at once.

I am scratching my head here. Please help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 06:30:34

what you need is to get the id values that you need to delete onto a temporary table. like


SELECT ID INTO #temp
FROM tblTrips
WHERE <your condition>

then join to this table on id to delete from others like

DELETE ts FROM tbltripsscheduled ts
JOIN #temp t
on t.id=ts.TripID
...
and finally from main table tbltrips

JMMILLS
Starting Member

2 Posts

Posted - 2009-09-02 : 10:17:45

I wanted to make this a stored procedure:

CREATE PROC dbo.deleteData
(@CutOffDate datetime)
AS
BEGIN
SET NOCOUNT ON

IF @CutOffDate IS NULL
BEGIN
SET @CutOffDate = DATEADD(mm, -12, CURRENT_TIMESTAMP)

END
ELSE
BEGIN
IF @CutOffDate > DATEADD(mm, -12, CURRENT_TIMESTAMP)
BEGIN
RAISERROR ('Cannot delete date from last tweleve months', 16, 1)
RETURN -1
END
END

BEGIN TRAN

SELECT ID INTO #ARCH
FROM tblTrips
WHERE effectiveenddate < @cutoffdate

DELETE TripID from tblTripsScheduled.TripID
Join #ARCH.ID
ON tblTripsScheduled.TripID = tblTrips.ID

DELETE TripID from tblTripsTemplate.TripID
Join #ARCH.ID
ON tblTripsTemplate.TripID = tblTrips.ID

DELETE TripID from tblTripsActual.TripID
Join #ARCH.ID
ON tblTripsActual.TripID= tblTrips.ID

DELETE ID from tblTrips
Join #ARCH.ID
ON tblTrips.ID = tblTrips.ID

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.tbltripsscheduled', 16, 1)
RETURN -1
END
COMMIT

sg 208, Level 16, State 1, Procedure OOO, Line 27
Invalid object name 'TripID'.
Msg 208, Level 16, State 1, Procedure OOO, Line 27
Invalid object name 'tblTripsScheduled.TripID'.
Msg 208, Level 16, State 1, Procedure OOO, Line 27
Invalid object name '#ARCH.ID'.
Msg 266, Level 16, State 2, Procedure OOO, Line 49
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
ID in tbltrips is the PK and TripID is the FK in all the other tables

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 10:21:41

[code]
CREATE PROC dbo.deleteData
(@CutOffDate datetime)
AS
BEGIN
SET NOCOUNT ON

IF @CutOffDate IS NULL
BEGIN
SET @CutOffDate = DATEADD(mm, -12, CURRENT_TIMESTAMP)

END
ELSE
BEGIN
IF @CutOffDate > DATEADD(mm, -12, CURRENT_TIMESTAMP)
BEGIN
RAISERROR ('Cannot delete date from last tweleve months', 16, 1)
RETURN -1
END
END

BEGIN TRAN datadelete

SELECT ID INTO #ARCH
FROM tblTrips
WHERE effectiveenddate < @cutoffdate

DELETE TripID from tblTripsScheduled
Join #ARCH a
ON a.ID=tblTripsScheduled.TripID

DELETE TripID from tblTripsTemplate
Join #ARCH a
ON a.ID=tblTripsTemplate.TripID

DELETE TripID from tblTripsActual
Join #ARCH a
ON a.ID=tblTripsActual.TripID

DELETE ID from tblTrips
Join #ARCH a
ON a.ID=tblTrips.TripID

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN datadelete
RAISERROR ('Error occured while deleting data from dbo.tbltripsscheduled', 16, 1)
RETURN -1
END
COMMIT TRAN datadelete
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources