2

I think I'm losing my mind here. I try to update a specific subset of Table1. Let's say the SELECT from Table1 returns 1 million rows. For each of these rows, I want to cross apply a specific calculated sum from Table2.

I know for a fact that not all rows in Table1 can be joined to Table2 with the conditions in the CROSS APPLY part.

The update still says 1,000,000 row(s) affected. After this I got suspicious and added the '1 = 0' condition to the CROSS APPLY so it could never return a row.

But it still updates all rows in Table1?

UPDATE T1
SET T1.Field1 = T2.SumField
FROM
    (
        SELECT *
        FROM Table1
        WHERE ....
    ) T1
CROSS APPLY
    (
        SELECT SUM(Field1) SumField
        FROM Table2
        WHERE [A lot of Fields] = [Some Values from T1]
        AND 1 = 0 -- !!!
    ) T2

Does anyone know why this happens?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Koruba
  • 173
  • 1
  • 9
  • 1
    I'm not sure why you're using `CROSS APPLY` for this, what's wrong with an `INNER` or a `LEFT JOIN` here? I'd say all your `WHERE 1 =0` did is return a NULL... – Shaneis Apr 26 '17 at 14:16
  • Microsoft will not feel good about your title. :P – Sankar Apr 26 '17 at 14:23

1 Answers1

1

The second query is not null set - it returns one value NULL which is joined with result. 

UPDATE T1
SET T1.Field1 = T2.SumField
FROM
       (
        SELECT *
        FROM Table1
        WHERE ....
    ) T1
CROSS APPLY
    (
        SELECT Field1 SumField
        FROM Table2
        WHERE [A lot of Fields] = [Some Values from T1]
        AND 1 = 0 -- !!!
    ) T2

you will get 0 rows affected (notice missing SUM)

nimdil
  • 1,361
  • 10
  • 20
  • 1
    Yes, you are right. It returns a NULL column if I use the SUM aggregate. Thank you, this helped restore my sanity :) – Koruba Apr 26 '17 at 14:41