Update table with inner join with an aggregate function with SQL statement-CodePudding

I have two tables and I need to update first table with max value from second table

Table1

item	value
item1	value1
item2	value2

table2

item	value
item1	value1
item1	value2
item2	value...

I use this SQL statement :

DoCmd.RunSQL "UPDATE table1 AS t1 INNER JOIN  " & _
"(SELECT table1.item, MAX(table2.value) AS maxvalue FROM table1 INNER JOIN table2  " & _ 
"ON table1.item = table2.item GROUP BY table1.item) AS t2  " & _
"ON t1.item = t2.item SET t1.value = t2.maxvalue "

I get an error

Operation must use an updateable query

if I take out max function from statement, SQL will execute, but I need max value

CodePudding user response：

I follow @june7 and yes, Access does not allow UPDATE action when aggregate query is involved.

I even tried to join table with recordset as well and that way is not possible also.

So one solution is to create temp table first and then update the first table.

So the solution for my question is:

DoCmd.RunSQL "SELECT table1.item, Max(table2.value) AS maxvalue INTO tempTBL " & _
 "FROM table1 INNER JOIN table2 ON table1.item = table2.item" & _
 "GROUP BY table1.item " & _
 "ORDER BY table1.item"

DoCmd.RunSQL "UPDATE table1 AS t1 INNER JOIN tempTBL AS t2 " & _
  "ON t1.item = t2.item " & _
  "SET t1.value = t2.maxvalue "