UPDATE - SET with aggregate functions Access SQL

Question

I have a tab with Agents and fields |ID_Agent | City | Payment| , i want to set their payments as a part of the sum of all agents in the concrete town.

UPDATED

To make shorter there is another call, but anyway, it doesn't work

UPDATE Agent as a1
    SET a1.Payment=  
    (SELECT AVG(a2.Payment) FROM Agent as a2 WHERE a2.City= a1.City)

I have read that it should work, but it doesn't so i don't know how to make this thing work
How it supposed to be:

Original table
|ID_Agent | City | Payment|  
|1        |London| 1600   |  
|2        |York  | 2000   |
|3        |London| 1000   |
|4        |York  | 1500   |

Result table
|ID_Agent | City | Payment|  
|1        |London| 1300   |  
|2        |York  | 1750   |
|3        |London| 1300   |
|4        |York  | 1750   |

You are trying to do `SET a1.Payment = (select avg(a2.Payment) from ...)`. However, this doesn't make much sense. Show us some sample table data and the expected result. (As formatted text, no images.) Take a look at [mcve] too. — jarlh, Oct 24 '19 at 07:07
Create a view instead, with the additional column city_avg_payment. — jarlh, Oct 24 '19 at 08:01

i.signori · Answer 1 · 2019-10-24T07:57:20.453

0

EDIT:

 UPDATE Agent as a1 
 SET a1.Payment = a2._AvgPayment 
 FROM
     ( SELECT 
              City ,
              AVG(Payment) _AvgPayment
       FROM Agent 
       GROUP BY City 
     ) as a2
 WHERE a1.City = a2.City

But I think you can take inspiration from this

edited Oct 24 '19 at 07:57

answered Oct 24 '19 at 07:12

i.signori

585
3
16

Trying to set each rows Payment to the city avg for that column. Makes no sense. – jarlh Oct 24 '19 at 07:30
You're right, let's hope it is useful to at least see how to update through aggregate functions – i.signori Oct 24 '19 at 07:40
Not work for me "Syntax error (missing operator) in query expression Table_A.sum / Table_b.count FROM .... AS Table_A". Gonna try with INNER – Vlad Samara Oct 24 '19 at 07:47

score 0 · Answer 2 · edited Oct 24 '19 at 08:12

0

First prepare the data by grouping. And then do the update by joining the table. so we dont hold the table long time. The update goes faster, and SQL dont prepare the data for each row. Since it is already prepared in CTE.

;with AgentCTE as (

    SELECT city , sum(Payment) as TotalPayment, count(city) as AgentCount
        FROM agent
        GROUP BY city

)
UPDATE agnt set agnt.payment = (CTE.TotalPayment / CTE.AgentCount)
FROM Agent agnt 
    JOIN AgentCTE CTE on (agnt.city = CTE.city);

edited Oct 24 '19 at 08:12

Andre

26,751
7
36
80

answered Oct 24 '19 at 07:54

Nats N

41
1

1

Access database can handle Common Table Expression (CTE) Queries ? – i.signori Oct 24 '19 at 07:58

UPDATE - SET with aggregate functions Access SQL

UPDATED

2 Answers2