Using Avg() with SQL 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!

Retagged to remove "avg" and "update" tags. Neither is a really a category IMO, and avg has also been used for the anti-virus software in another post. Feel free to put them back if anyone disagrees. — John M Gant, May 21 '09 at 16:36
@Bryan, I really don't understand what is wrong with people on StackOverflow today - I've seen a lot of bizarre downvotes. This is a good question, well written, nicely formatted. Can't understand why it's been downvoted. I upvoted it for you. — Rob, Jun 12 '09 at 20:23
my guess as for the downvote reason would be the "yet another ms-access sql question" part of the title... — dotjoe, Jun 12 '09 at 20:54
@unforgiven3 Oh it's not just today. Someone has been harassing me by down-voting all of my posts for a few days now because I posted a suggestion he didn't like and a few others have since then been doing the same. — Bryan, Jun 12 '09 at 20:55
@dotjoe I had used that because it was about the 3rd ms-access sql question I had asked in a day or two. But if you think that is the reason then I have no problems removing it and thanks for the suggestion. — Bryan, Jun 12 '09 at 20:58

score 0 · Accepted Answer · answered May 21 '09 at 16:36

0

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

answered May 21 '09 at 16:36

DJ.

16,045
3
42
46

I need all of the information from all of the queries im doing to be continuously stored in tables because all the information in the table is what i need, not just the avg(RSSI) and once im done the data is viewed in a form with a list box that uses a table as it's source. And since this is a project i inherited from my predecessor, im being forced to do things in much the same way – Bryan May 21 '09 at 18:00

score 0 · Answer 2 · edited May 23 '17 at 09:57

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

score 0 · Answer 3 · answered Jun 13 '09 at 10:20

0

See:

ACC: Update Query Based on Totals Query Fails

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

answered Jun 13 '09 at 10:20

onedaywhen

55,269
12
100
138

Using Avg() with SQL update

3 Answers3