i want to update a table with a field name rate 1 from another table’s fields using average function?
table A
sr_no provider_id rating
1 a null
2 b null
3 c null
table B
sr_no provider_id rating
1 a 1
2 b 2
3 c 3
4 a 1
5 b 2
6 c 3
7 a 1
8 b 2
9 c 3
10 a 1
11 b 2
12 c 3
please solve my problem
thank you
i want to find the average of each provider from table B and update in the rating column of table A
the query that i wrote and doesn’t work is as follows:
update table A
set rating = (select avg(rating) from table group by provider_id)
FROM table A INNER JOIN
table B ON table A.Provider_id = table B.Provider_id
This should work
UPDATE [TABLE A]
SET Rating = AvgRating
FROM [TABLE A] INNER JOIN
(
SELECT
Provider_id,
AVG(Rating) AvgRating
FROM
[TABLE B]
GROUP BY
Provider_id
) Averages
ON [TABLE A].Provider_id = Averages.Provider_id
This should work
UPDATE [TABLE A]
SET Rating = AvgRating
FROM [TABLE A] INNER JOIN
(
SELECT
Provider_id,
AVG(Rating) AvgRating
FROM
[TABLE B]
GROUP BY
Provider_id
) Averages
ON [TABLE A].Provider_id = Averages.Provider_id
References :