'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 |
---|