Update with aggregate-CodePudding

I'm having trouble changing an update from Oracle to SQL Server

I have doubt in an update in SQL Server. In Oracle, I managed to do it, but in SQL Server, I'm not getting it.

Below are the two examples (Oracle worked, however SQL Server returns the error:

An aggregate may not appear in the list of sets of an UPDATE statement.

Oracle

UPDATE
  tab1
SET
  (t1.va, t1.vb) = (
    SELECT
      sum(
        CASE
          WHEN (t2.date2 - t2.date1) <= 30 THEN Coalesce((t2.x   t2.y), 0)
        END
      ) * 100,
      sum(
        CASE
          WHEN (t2.date2 - t2.date1) <= 60
          AND (t2.date2 - t2.date1) > 30 THEN Coalesce((t2.y   t2.z), 0)
        END
      ) * 100
    FROM
      tab2 t2
    WHERE
      t2.cd_xyz = tab1.cd_xyz
  );

SQL Server

UPDATE
  tab1 t1
SET
  t1.va = sum(
    CASE
      WHEN (t2.date2 - t2.date1) <= 30 THEN COALESCE((t2.x   t2.y), 0)
    END
  ) * 100,
  t1.vb = sum(
    CASE
      WHEN (t2.date2 - t2.date1) <= 60
      AND (t2.date2 - t2.date1) > 30 THEN COALESCE((t2.y   t2.z), 0)
    END
  ) * 100
FROM
  tab2 t2
  INNER JOIN tab1 t1 ON t2.cd_xyz = t1.cd_xyz;

CodePudding user response：

You need to calculate the values you want to use in a sub-query then join then onto the table you are updating using the UPDATE ... FROM syntax from the documentation.

I'm not totally sure I've expressed your logic correctly given you didn't have any grouping, but did have a sum, but it should be enough to get you started.

UPDATE tab1 SET
    va = t2a.vaNew
    , vb = t2a.vbNew
FROM tab1
INNER JOIN (
    SELECT
        cd_xyz
        , SUM(
          CASE
              WHEN (t2.date2 - t2.date1) <= 30 THEN COALESCE((t2.x   t2.y), 0)
          END
        ) * 100 as vaNew
        , SUM(
          CASE
              WHEN (t2.date2 - t2.date1) <= 60
              AND (t2.date2 - t2.date1) > 30 THEN COALESCE((t2.y   t2.z), 0)
          END
        ) * 100 AS vbNew
    FROM tab2 t2
    GROUP BY cd_xyz
) AS t2a ON t2a.cd_xyz = t1.cd_xyz;