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