Using Avg () with SQL Update

Question

I have a table with several readings_miu_id, each with multiple RSSI readings (RSSI is also a field name). So, currently I have a data table with many columns, but the two corresponding 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

etc.

My initial plan was to change the RSSI value for each post with the same readings_miu_id with the average RSSI for this readings_miu_id (which should look the same as above, except that the separate RSSI will be replaced by the average RSSI for this miu) and then pull out only one entry for each individual readings_miu_id (which I'm pretty sure I can do this with the select top 1 statement). However, I am having trouble calculating the first part. The sql statements I tried look like they should be close:

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

Answer 1

Not sure why you want to update records.

If you just want to read avg, you can do this:

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

Answer 2

. question, .

, , - ( SQL , ):

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

Cm:

ACC: Update Query Based on Results of Totals Query

"This behavior is a limited design ... There are three methods for working with this behavior ..."