What is the best way to delete a joined association to a stored file in a database?

Question

I want to know the sql statement(s) to use to delete old data and the association to the old data without deleting the more important parent table, which should live forever.

I have the following tables:

Step
  Id bigint
  OccurredOn datetime
  ScreenshotId bigint

Screenshot
  Id bigint
  Data varbinary(max)

I want to delete all Screenshots records associated with Steps where OccurredOn is more than 30 days old. The Steps would still be persisted with a null ScreenshotId.

What is the best SQL Statement(s) to do this?

Answer 1

Use:

UPDATE STEP
   SET screenshotid = NULL
 WHERE occurredon < DATEADD(dd, -30, GETDATE())

DELETE FROM SCREENSHOT
 WHERE NOT EXISTS(SELECT NULL
                    FROM STEP s
                   WHERE s.screenshotid = id)

Answer 2

Unfortunately, as far as I can tell, you cannot use the OUTPUT clause chaining directly, but this method (live runnable version) ensures that you will only actually delete any screenshot rows you just orphaned (as opposed to any existing orphans):

-- SO3208939
CREATE TABLE #Screenshot (
    Id bigint IDENTITY(100000, 10) NOT NULL
    ,Data varbinary(max) NULL
)

CREATE TABLE #Step (
    Id bigint IDENTITY NOT NULL
    ,OccurredOn datetime NOT NULL
    ,ScreenshotId bigint NULL REFERENCES #Screenshot(Id)
)

INSERT INTO #Screenshot DEFAULT VALUES
INSERT INTO #Step VALUES ('1/1/2010', @@IDENTITY)
INSERT INTO #Screenshot DEFAULT VALUES
INSERT INTO #Step VALUES ('2/1/2010', @@IDENTITY)
INSERT INTO #Screenshot DEFAULT VALUES
INSERT INTO #Step VALUES ('3/1/2010', @@IDENTITY)
INSERT INTO #Screenshot DEFAULT VALUES
INSERT INTO #Step VALUES ('4/1/2010', @@IDENTITY)
INSERT INTO #Screenshot DEFAULT VALUES
INSERT INTO #Step VALUES ('5/1/2010', @@IDENTITY)
INSERT INTO #Screenshot DEFAULT VALUES
INSERT INTO #Step VALUES ('6/1/2010', @@IDENTITY)
INSERT INTO #Screenshot DEFAULT VALUES
INSERT INTO #Step VALUES ('7/1/2010', @@IDENTITY)
INSERT INTO #Screenshot DEFAULT VALUES
INSERT INTO #Step VALUES ('8/1/2010', @@IDENTITY)

-- SELECT * FROM #Screenshot
-- SELECT * FROM #Step

DECLARE @Deleted AS TABLE (Id bigint NOT NULL)

UPDATE #Step
SET ScreenshotId = NULL
OUTPUT DELETED.ScreenshotId
INTO @Deleted
WHERE OccurredOn < DATEADD(dd, -30, GETDATE())

DELETE FROM #Screenshot
WHERE Id IN (SELECT Id FROM @Deleted)

-- SELECT * FROM #Step
SELECT * FROM #Screenshot

​