Updating Table with data from a Query Operation Must Use an Updateable Query

Question

was wondering if anyone can help me out with this. I have a table and a query both of which have the same [Resource ID], [Resource Name] fields. I am trying to update the [CBL_1_Date], and the [CBL_1_kW] fields in the table with the [CBL_x_Date] and [CBL_x_kW] fields of the query. The problem is that when I try to run the query I keep getting "Operation Must Use an Updateable Query" error.

UPDATE tCBLAggregation 
INNER JOIN qBestof4CBLs_avg 
ON (tCBLAggregation.[Event Date] = qBestof4CBLs_avg.[Event Start Date]) 
      AND 
      (tCBLAggregation.[Resource ID] = qBestof4CBLs_avg.[Resource ID]) 
SET tCBLAggregation.CBL_1_Date = [qBestof4CBLs_avg].[CBL_x_Date], tCBLAggregation.CBL_1_kW = [qBestof4CBLs_avg].[AvgOfCBL_x_kW];

What database server/version? – David-SkyMesh Jan 07 '14 at 02:30 — David-SkyMesh, Jan 07 '14 at 02:30
Looks like `SQL Server` or `MS Access`. – PM 77-1 Jan 07 '14 at 02:33 — PM 77-1, Jan 07 '14 at 02:33

score 0 · Answer 1 · edited May 23 '17 at 10:32

If you use a sub query for qBestof4CBLs_avg then you're not trying to update a non-updatable table expression.

In ANSI SQL (e.g: PostgreSQL / SQL Server):

UPDATE "tCBLAggregation" 
   SET "CBL_1_Date" = v.d, 
       "CBL_1_kW"   = v.x
  FROM (
          SELECT "Event Start Date" as esd, 
                 "Resource ID"      as rid,
                 "CBL_x_Date"       as d, 
                 "AvgOfCBL_x_kW"    as x
          FROM   "qBestof4CBLs_avg"
       ) v 
 WHERE "Event Date"  = v.esd
   AND "Resource ID" = v.rid;

Should be very similar in any reasonably ANSI SQL compliant database.

On reflection, UPDATE ... INNER JOIN looks like it might be MySQL (which is... not so compliant).

See @Eric 's answer in this SO question for translations to other databases.

edit: removed confusing x table alias in sub-query.

Updating Table with data from a Query Operation Must Use an Updateable Query

1 Answers1