title: MS-ACCESS: Deleting all rows except for top 1 and updating a table from a query
body: <p>I'm almost done with this, just a few last hiccups. I now need to delete all records from a table except for the top 1 where readings_miu_id is the "DISTINCT" column. In other words words i need to delete all records from a table other than the first DISTINCT readings_miu_id. I am assuming all I need to do is modify the basic delete statement:</p>
  
  <pre><code>DELETE FROM analyzedCopy2
  WHERE readings_miu_id = some_value
  </code></pre>
  
  <p>But I can't figure out how to change the some_column=some_value part to something like:</p>
  
  <pre><code>where some_column notequal to (select top 1 from analyzedCopy2 as A 
  where analyzedCopy2.readings_miu_id = A.readings_miu_id)
  </code></pre>
  
  <p>and then I need to figure out how to use an UPDATE statement to update a table (analyzedCopy2) from a query (which is where all of the values I want stored into column RSSI in table analyzedCopy2 are currently located). I've tried this:</p>
  
  <pre><code>UPDATE analyzedCopy2 from testQuery3 SET analyzedCopy2.RSSI = 
  (select AvgOfRSSI from testQuery3 INNER JOIN  analyzedCopy2 on analyzedCopy2.readings_miu_id =  testQuery3.readings_miu_id where analyzedCopy2.readings_miu_id = testQuery3.readings_miu_id) 
  where analyzedCopy2.readings_miu_id = testQuery3.readings_miu_id
  </code></pre>
  
  <p>but apparently I can't use FROM inside of an update statement. Any thoughts?</p>
  
  <p>I'm sure I'm going about this a very nonstandard (and possibly if not probably the flat out wrong) way but I'm not being allowed to use vb.net2008 to pull and manipulate then store the data like I would like to so I'm stuck right now using sql statements in ms-access which is a good learning experience (Even if trying to do such odd things as I've been having to do in sql statements is making me beat my head against my deck <em>figuratively of course</em>)</p>