T-SQL MERGE: Each GROUP BY expression must contain at least one column that is not an outer reference

Question

When using SELECT CONCAT(SOURCE.OrderNo, '_', SOURCE.OrderLine), MAX(SOURCE.LastDate) GROUP BY CONCAT(SOURCE.OrderNo, '_', SOURCE.OrderLine) and SELECT CONCAT(TARGET.OrderNo, '_', TARGET.OrderLine), MAX(TARGET.LastDate) GROUP BY CONCAT(TARGET.OrderNo, '_', TARGET.OrderLine) in

MERGE dbo.TargetTbl AS TARGET
USING dbo.SourceTbl AS SOURCE 
      ON (TARGET.OrderNo = SOURCE.OrderNo) 
WHEN MATCHED AND EXISTS (SELECT           CONCAT(SOURCE.OrderNo, '_', SOURCE.OrderLine)
                                         ,MAX(SOURCE.LastDate)
                         GROUP BY         CONCAT(SOURCE.OrderNo, '_', SOURCE.OrderLine)
                         INTERSECT SELECT CONCAT(TARGET.OrderNo, '_', TARGET.OrderLine)
                                         ,MAX(TARGET.LastDate)
                         GROUP BY         CONCAT(TARGET.OrderNo, '_', TARGET.OrderLine)
                        )
THEN UPDATE SET TARGET.IsBlocked = 1;

I get this error:

Each GROUP BY expression must contain at least one column that is not an outer reference.

I searched and found some solutions, but none of them is applicable to my query or at least I don't know how to. Any help would be greatly appreciated.

EDIT: I definitely can have two identical rows in the SOURCE & TARGET tables and it throws me this error:

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

By adding GROUP BY it gives me the outer reference error mentioned above.

score 1 · Answer 1 · answered Sep 04 '18 at 14:46

1

You dont need the INTERSECT, because the Merge compares your data already between the Source and the Target tables. Try something as below.

MERGE dbo.TargetTbl AS TARGET
USING 
(
    SELECT  CONCAT(OrderNo, '_', OrderLine) AS OrderLineNo,
            MAX(LastDate) AS LastDate
    FROM dbo.SourceTbl
    GROUP BY CONCAT(SOURCE.OrderNo, '_', SOURCE.OrderLine)
) AS SOURCE 
    ON SOURCE.OrderNo = TARGET.OrderNo 
    AND SOURCE.OrderLineNo = CONCAT(TARGET.OrderNo, '_', TARGET.OrderLine)
    AND SOURCE.LastDate = TARGET.LastDate
WHEN MATCHED THEN UPDATE 
        SET TARGET.IsBlocked = 1;

answered Sep 04 '18 at 14:46

mvisser

652
5
11

I managed to do it without the MAX & GROUP BY, but I need to do some tests in order to verify if it's error proof. How does your code handle NULLs if you don't use SELECT ... INTERSECT/EXCEPT SELECT? I used this guide: http://sqlblog.com/blogs/paul_white/archive/2011/06/22/undocumented-query-plans-equality-comparisons.aspx – Tomo Sep 05 '18 at 09:03
Sorry I'm a bit confused about "code handle NULLs." I need to know what you want to achieve? Because the script about makes a comparison between the Target and the source table. If any of the ON conditions meet then seems like you want to block the Order. But seems like there is more. than you wan than above. – mvisser Sep 05 '18 at 10:54
We can add additional statements to the MERGE. for example EXCEPT, if the the record in in the SOURCE table but not in the TARGET table then do you want to Insert the record? Or if the record is in TARGET table but not in the SOURCE table do you want the record to be DELETED in the TARGET table? – mvisser Sep 05 '18 at 10:58
I omitted the WHEN NOT MATCHED part because it's not relevant. I could do a basic 'when records are matched, update the records if there is any (or no) change', but in order to handle nulls correctly I used INTERSECT instead as suggested here: https://stackoverflow.com/questions/4509722/nulls-and-the-merge-statement-i-need-to-set-a-value-to-infinity-how Everything fine to this point. – Tomo Sep 05 '18 at 12:58
It's when I added the MAX & GROUP BY in the two SELECTs (the INTERSECT part), that it threw the outer reference error. I found a workaround and hopefully won't need to use the MAX & GROUP BY keywords. – Tomo Sep 05 '18 at 13:02
Ah ok I see now I understand what you were trying to do. – mvisser Sep 05 '18 at 13:18
Looks like I can have duplicate records in my tables, so I need to added te GROUP BY somehow. I still need help. – Tomo Sep 06 '18 at 08:03

T-SQL MERGE: Each GROUP BY expression must contain at least one column that is not an outer reference

1 Answers1