Proc Comment Proc Comment - 15 days ago 7
SQL Question

Why would a SQL Server SELECT statement work within a SET statement, but fail on its own?

The SQL below works fine in the following context:

USE ThisDB

UPDATE guest.TABLE2
SET this_var = (SELECT count(T1.key_field)
FROM ThisDB..TABLE1 T1
WHERE T1.key_field = guest.TABLE2.keyfield
AND T1.date_field between (DATEADD(DAY, -7, guest.TABLE2.other_date)) and guest.TABLE2.other_date)


However, on its own, the
SELECT
statement (below) fails with error


The multi-part identifier 'guest.TABLE2.keyfield' could not be bound. (4104)


SELECT
count(T1.key_field)
FROM
ThisDB..TABLE1 T1
WHERE
T1.key_field = guest.TABLE2.keyfield
AND T1.date_field BETWEEN (DATEADD(DAY, -7, guest.TABLE2.other_date))
AND guest.TABLE2.other_date

Answer
SELECT
    (
    SELECT count(T1.key_field)
    FROM ThisDB..TABLE1 T1
    WHERE   T1.key_field = guest.TABLE2.keyfield
        AND T1.date_field between
            DATEADD(DAY, -7, guest.TABLE2.other_date) and guest.TABLE2.other_date
    ) as newValue
FROM guest.TABLE2;

One simple way to convert your update to a select. Essentially I converted the set to select and move the table name into a from clause.

You could also look into using range with count() over () if you have a later version of SQL Server. It appears that you're fairly new to SQL so that would be an advanced concept.

The reason that the subquery will not run on its own is as I mentioned above in a comment. The inner query refers to a column of the outer query, which is called correlation. When you pull that out and attempt to run it separately there is no longer any way to resolve that reference.

Comments