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.
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 & "'")
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
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
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.
