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:

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.

CodePudding user response：

Break down your query bit by bit.

On the face of it this bit looks fine

select * FROM [dbo].websiteTestLocation wtl
LEFT JOIN [dbo].website w ON w.websiteRecordId = wtl.websiteRecordId

But that next JOIN is all over the place. It looks like you are trying to limit a LEFT OUTER JOIN to just the records that are in table website

LEFT JOIN [dbo].websiteSnapshot snap ON snap.websiteSnapshotId IN (w.websiteSnapshotId)

Use an INNER JOIN instead and this ON clause

ON snap.websiteSnapshotId = w.websiteSnapshotId

Next your WHERE clause - which is where the problem you report comes from. You are trying to match a single value to a list of values, that will never work. All of this

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

can be replaced with

WHERE websiteSnapshotStartTime < DATEADD(day, -1, GETDATE())