Update value SQL table from another table

Question

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!!

Still unsure of what you trying to achieve here, what does this data represent if certain column names and values are the same? where are you getting or calculating the the values that need to be updated in table 2? sorry for the questions but the schema is a little confusing and I want to make sure I understand what you trying to achieve before attempting to answer — Nate1zn
– Nate1zn, Commented Dec 11, 2022 at 20:38
thanks for the answer, Columns A and C are the same in the two tables, the values are representations can be any name, the values of table 2 are obtained from table 1, sum the times that the match is repeated A,B,C from table 1 and the status is 'OK' in 'sum_ok' into table 2, the same as in 'avg_ok' but taking the mean of the value field — crist_9
– crist_9, Commented Dec 11, 2022 at 21:22

lemon · Accepted Answer · 2022-12-11 21:17:41Z

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.

Collectives™ on Stack Overflow

Update value SQL table from another table

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related