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.
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] |
 |
|
|
|
|
|
|