'Getting all SQL Server database records older than X days

I am working on a query to delete records from the websiteTestLocation table (where websiteSnapshotStartTime is older than X days).

The (truncated) table structures look like this:

Table name Column
websiteSnapshot websiteSnapshotRecordId
websiteSnapshot websiteSnapshotStartTime
website websiteSnapshotRecordId
website websiteRecordId
websiteTestLocation websiteRecordId

The websiteTestLocation table's "websiteRecordId" is linked to the same column in the website table and website table's "websiteSnapshotRecordId" is linked to the same column in the websiteSnapshot table.

I can get all of the websiteSnapshot records (older than 1 day) using:

SELECT (websiteSnapshotId)
    FROM [dbo].websiteSnapshot
    WHERE websiteSnapshotStartTime IN (
        SELECT
            (websiteSnapshotStartTime)
        FROM
            [dbo].websiteSnapshot
        WHERE
            websiteSnapshotStartTime < DATEADD(day, -1, GETDATE())
    )

But when I include that in the rest of my query, I get the error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The whole query looks like this:

--DELETE FROM [dbo].websiteTestLocation wtl
select * FROM [dbo].websiteTestLocation wtl
LEFT JOIN [dbo].website w ON w.websiteRecordId = wtl.websiteRecordId
LEFT JOIN [dbo].websiteSnapshot snap ON snap.websiteSnapshotId IN (w.websiteSnapshotId)
WHERE (SELECT (websiteSnapshotId)
    FROM [dbo].websiteSnapshot
    WHERE websiteSnapshotStartTime IN (
        SELECT 
            (websiteSnapshotStartTime)
        FROM
            [dbo].websiteSnapshot
        WHERE
            websiteSnapshotStartTime < DATEADD(day, -1, GETDATE())
    )) = snap.websiteSnapshotId
GO

I understand that, because a "foreach" loop would help, I must be doing something wrong. If I put "MAX" in front of "websiteSnapshotId" on line 5 and in front of websiteSnapshotStartTime on line 9, then I get data, but not all of the expected rows. I only get data from records with the "newest" websiteSnapshotStartTime that is older than 1 day.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source