CODEWITHSUNDEEP
sqlms-accessinner-join
Oldřich Švéda
Oldřich Švéda

Reputation: 107

UPDATE query with inner joined query

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.

Upvotes: 1

Views: 93

Answers (4)

Gord Thompson
Gord Thompson

Reputation: 123484

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

Upvotes: 2

TheGuyThatDoesn'tKnowMuch
TheGuyThatDoesn'tKnowMuch

Reputation: 1250

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.

Upvotes: 0

F.Abdelilah
F.Abdelilah

Reputation: 36

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

Upvotes: 0

Ashley Kurkowski
Ashley Kurkowski

Reputation: 206

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

Upvotes: 0

Related Questions