CODEWITHSUNDEEP
sqlsql-servert-sql
seebiscuit
seebiscuit

Reputation: 5053

Referencing FROM subquery alias inside CASE from UPDATE SET

Can anyone help me understand why this operation

UPDATE
    [Case_11268].[dbo].[Annotations]
SET
    [usercreated_id] = CASE 
        WHEN 
             EXISTS (SELECT id from gu where anno.[usercreated_id] = gu.id)
        THEN 
            'SOMEVALUE'
        ELSE 
            [usercreated_id] 
        END
FROM (
    SELECT 
        agu.userid, us.id 
    FROM
        [Case_11268].[dbo].[Annotations] anno
    INNER JOIN 
        [TSI_Main].[dbo].aspnet_Users us
    INNER JOIN 
        [TSI_Main].[dbo].aspnet_Membership mem on us.userid = mem.userid
    INNER JOIN
        [TSI_Main].[dbo].AdminGroupUsers agu on mem.userid = agu.userid
    INNER JOIN
        [TSI_Main].[dbo].AdminGroups ag on agu.adminGroup = ag.id
    WHERE
        ag.[deleted] = 0
    GROUP BY agu.userid, us.id
) AS gu

Throws this error:

Msg 208, Level 16, State 1, Line 6
Invalid object name 'gu'.

Upvotes: 1

Views: 48

Answers (1)

Martin Smith
Martin Smith

Reputation: 453028

There's almost certainly a much simpler way of writing this. Possibly

UPDATE [Case_11268].[dbo].[Annotations]
SET    [usercreated_id] = 'SOMEVALUE'
WHERE  [usercreated_id] IN (SELECT us.id
                            FROM   [TSI_Main].[dbo].aspnet_Users us
                                   INNER JOIN [TSI_Main].[dbo].aspnet_Membership mem
                                           ON us.userid = mem.userid
                                   INNER JOIN [TSI_Main].[dbo].AdminGroupUsers agu
                                           ON mem.userid = agu.userid
                                   INNER JOIN [TSI_Main].[dbo].AdminGroups ag
                                           ON agu.[adminGroup] = ag.id
                            WHERE  ag.[deleted] = 0)

but with regards to your question moving gu to a CTE puts it in scope.

WITH gu
     AS (SELECT agu.userid,
                us.id
         FROM   [TSI_Main].[dbo].aspnet_Users us
                INNER JOIN [TSI_Main].[dbo].aspnet_Membership mem
                        ON us.userid = mem.userid
                INNER JOIN [TSI_Main].[dbo].AdminGroupUsers agu
                        ON mem.userid = agu.userid
                INNER JOIN [TSI_Main].[dbo].AdminGroups ag
                        ON agu.[adminGroup] = ag.id
         WHERE  ag.[deleted] = 0
         GROUP  BY agu.userid,
                   us.id)
UPDATE anno 
SET    [usercreated_id] = CASE
                            WHEN EXISTS (SELECT id
                                         FROM   gu
                                         WHERE  anno.[usercreated_id] = gu.id) THEN 'SOMEVALUE'
                            ELSE [usercreated_id]
                          END
FROM   [Case_11268].[dbo].[Annotations] anno

A derived table can only be selected FROM when the derived table immediately precedes the alias. So you would need to move the whole definition to use the derived table there.

UPDATE anno 
SET    [usercreated_id] = CASE
                            WHEN EXISTS (SELECT id
                                         FROM   (SELECT agu.userid,
                                                        us.id
                                                 FROM   [TSI_Main].[dbo].aspnet_Users us
                                                        INNER JOIN [TSI_Main].[dbo].aspnet_Membership mem
                                                                ON us.userid = mem.userid
                                                        INNER JOIN [TSI_Main].[dbo].AdminGroupUsers agu
                                                                ON mem.userid = agu.userid
                                                        INNER JOIN [TSI_Main].[dbo].AdminGroups ag
                                                                ON agu.[adminGroup] = ag.id
                                                 WHERE  ag.[deleted] = 0
                                                 GROUP  BY agu.userid,
                                                           us.id) AS gu
                                         WHERE  anno.[usercreated_id] = gu.id) THEN 'SOMEVALUE'
                            ELSE [usercreated_id]
                          END
FROM   [Case_11268].[dbo].[Annotations] anno

Upvotes: 3

Related Questions