Using Avg() with update

Question

I have a table with multiple readings_miu_id's each with multiple RSSI readings (RSSI is also the name of the field). So, i currently have a datasheet, with many columns, but the two pertinent ones to this conversation look something like this:

readings_miu_id  RSSI
===============  ====
11011032         -90
11011032         -81
11011032         -62
11011032         -84
11011032         -86
11010084         -84
11010084         -86
11010084         -87

and so on.

My original plan was to change the value of RSSI for each record having the same readings_miu_id with the average RSSI for that readings_miu_id (which should look the same as above except that the individual RSSI's will be replaced by the average RSSI for that miu), and then pull only one record for each distinct readings_miu_id (which I'm pretty sure I can do with the select top 1 type statement.) However I'm having problems figuring the first part out. The sql statements I've tried that seem like they should be close are:

UPDATE analyzedCopy2 as A 
SET analyzedCopy2.RSSI = Avg(RSSI) 
where readings_miu_id = A.readings_miu_id

and

UPDATE analyzedCopy2 as A 
SET RSSI = Avg(select RSSI from analyzedCopy2 
    where readings_miu_id = A.readings_miu_id) 
WHERE readings_miu_id = A.readings_miu_id;

Help please!

Answer 1

Not sure why you want to update the records.

If you just want an avg reading you can do this:

SELECT readings_miu_id, AVG(RSSI)
FROM analyzedCopy2 
GROUP BY readings_miu_id

Answer 2

Please see this question, where a similar scenario is discussed.

The query you are looking for is something like this (I don't have an SQL shell here so there may be slight syntax problems):

UPDATE analyzedCopy2 AS target
INNER JOIN 
(
    select avg(RSSI) as AvgRSSI, readings_miu_id
    from analyzedCopy2 T
    group by readings_miu_id
) as source
ON target.readings_miu_id = source.readings_miu_id
SET target.RSSI = source.AvgRSSI

Answer 3

See:

ACC: Update Query Based on Totals Query Fails

"This behavior is a design limitation... There are three methods of working around this behavior..."