UPDATE query with inner joined query

Question

I haved saved SELECT query. I need create update query to update table field with value from saved select query.

Im getting error "Operation must use an updatable query". Problem is that saved select query result not contain primary key.

UPDATE [table] INNER JOIN
       [saved_select_query]
       ON [table].id_field = [saved_select_query].[my_field]
    SET [table].[target_field] = [saved_select_query]![source_field]);

Im also try with select subquery instead of inner join, but same error.

Answer 1

Perhaps a DLookUp() will do the trick:

UPDATE [table] SET
    [target_field] = DLookUp("source_field", "saved_select_query", "my_field=" & id_field)

... or, if the joined field is text ...

UPDATE [table] SET
    [target_field] = DLookUp("source_field", "saved_select_query", "my_field='" & id_field & "'")

Answer 2

I'm not sure I completely understand what you are asking. If you are asking what syntax to use when performing an update with an inner join.

UPDATE      tableAlias
SET         Column = Value
FROM        table1 as tableAlias
INNER JOIN  table2 as table2Alias on tableAlias.key = table2Alias.key

Answer 3

your query is incorrect , try this.

  UPDATE [table]

  SET [table].[target_field] = [table2]. 
  [source_field])

   from (select *from  
   [table] INNER JOIN
   [saved_select_query] a
   ON [table].id_field = 
  a.[my_field] )    
  table2 

Answer 4

I got it to work using the following:

UPDATE [table]
SET [table].[target_Field] = (SELECT [source_field] FROM [Saved_Select_Query]
WHERE [table].[id_field] = [Saved_Select_Query].[my_field])

You can't use an JOIN on an UPDATE statement directly, so you need to join the tables in a subquery.