Update value SQL table from another table-CodePudding

I have this tables:

I need to update sum_ok and avg_ok considering values from table 1 like this:

I have this code SQL, but don't work fine:

update 
    t2
set sum_ok = sum(case when t2.[status]='OK' then 1 else 0 end )
    ,avg_ok = avg(case when t2[status]='OK' then status end  )
from t1
    inner join t2
    on t1.A = t2.A --and t1.C = t2.C
where C is not null
group by A, C

Thanks!!

CodePudding user response：

One option is to compute your "sum_ok" and "avg_ok" values separately, then apply the UPDATE statement while joining your "t2" table and your computed values:

WITH cte AS (
    SELECT A, 
           C,  
           SUM(CASE WHEN [status] = 'ok' THEN 1 END) AS sum_ok,
           AVG(CAST ([value] AS FLOAT)) AS avg_ok
    FROM t1
    GROUP BY A, C
)
UPDATE t2
SET t2.sum_ok = cte.sum_ok,
    t2.avg_ok = cte.avg_ok
FROM       t2
INNER JOIN cte
        ON t2.A = cte.A AND t2.C = cte.C;

Check the demo here.

Note: in your query you're trying to access the "t2.status" field, whereas "t2" table doesn't have it.