Update Query using value in field 2 of Lookup table? 2

[Kobayashi]

I have a two tables. Table 1 contains my data and Table 2 is a lookup table containg 2 fields (columns) of values.

I would like to create an update query that will try to match the values of a field in Table 1 to the values in field 1 of the lookup table. If a match exists I'd like to then change (update) the value of the same field in Table 1 to the value of field 2 in the lookup table?

Hope this makes sense? I'm currently doing this in Excel and need to try and replicate in Access (sql).

Any help/pointers would be appreciated.

Thanks,

Adrian

 

[PHV]

Something like this ?
UPDATE Table1 SET Field1=(SELECT Field2 FROM Table2 WHERE Table2.Field1=Table1.Field1)
WHERE Field1 IN (SELECT Field1 FROM Table2)
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Kobayashi]

PH,

Thanks for the very prompt response!
This would help a great deal indeed, if I could only get it to work?

Below is what I've put in but I'm getting an 'Operation must use an updateable query' error? Would appreciate it if you could review and let me know if I've made (obviously have) any mistakes? Please ignore the poor normalisation. I'm just trying to get it to work and will renmame the fields and tables appropriately afterwards.

Thanks again,

Adrian

 

[Kobayashi]

Would help if I included my query I suppose!

UPDATE [Make table test]
SET [Make table test].Field12=(SELECT Value FROM [PB Trans type lookup table] WHERE [PB Trans type lookup table].value=[make table test].Field12)
WHERE [Make table test].Field12 IN (SELECT [Pb trans type] FROM [PB Trans type lookup table]);

 

[Kobayashi]

Amended but still not working?

UPDATE [Make table test]
SET Field12 =(SELECT [Value] FROM [PB Trans type lookup table] WHERE [PB Trans type lookup table].[pb trans type]=[Make table test].Field12)
WHERE Field12 In (SELECT [pb trans type] FROM [PB Trans type lookup table]);

 

[PHV]

And this ?
UPDATE [Make table test]
SET Field12 = Nz((SELECT [Value] FROM [PB Trans type lookup table] WHERE [pb trans type]=Field12), Field12);

 

[Kobayashi]

PHV,

I'm still getting the 'Operation must use an updateable query' error message?

Appreciate your help with this.

Regards,

Adrian

 

[Kobayashi]

PHV/Anyone!

Found the below in an article, which I think is perhaps why the query doesn't work but I'm nowhere near experienced enough to convert the SQL provided by PHV into what, seemingly, is acceptable to Access?

Any help would be much appreciated!

Regards,

Adrian

Access does not support correlated subqueries in SQL UPDATE statements for the purpose of producing aggregate values for SET clauses or for filtering rows returned by the parent statement using IN or EXISTS. If you try, you will see the message "Operation must use an updateable query." However, Access will let you specify multiple tables in an UPDATE query and join them using WHERE-based criteria or a JOIN clause. The JOIN syntax is a little weird. It immediately precedes the SET clause, and is different than SQL Server's T-SQL (though T-SQL may support the Access/JET syntax; I'm not sure which is standard). In T-SQL you can do this:

UPDATE tbl1
SET Field2 = tbl2.Field1 * tbl3.Field1 FROM tbl2
INNER JOIN tbl3 ON tbl2.Field1 = tbl3.Field1

In Access, you would use something like this:

UPDATE (tbl1 INNER JOIN tbl2 ON tbl1.ID = tbl2.ID)
INNER JOIN tbl3 ON tbl1.ID = tbl3.ID
SET tbl1.Field2 = tbl2.Field1 * tbl3.Field2

 

[PHV]

So, you may try something like this:
UPDATE [Make table test] INNER JOIN [PB Trans type lookup table] ON Field12=[pb trans type]
SET Field12 = [PB Trans type lookup table].[Value]
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

If PHV's SQL still gives you the "Operation must use an updateable query" and you don't have a primary key or at least a unique index on each table, then add the primary key or unique index and run the query again.

 

[Kobayashi]

PHB,

Many thanks for persevering with this/me, seems to work a treat!

JonFer,

Thanks also but my table does have a Primary key so all worked fine.

All the best and thanks again!

Adrian

 

[Genimuse]

Thanks PHV, a year later a search found your helpful answer!

I love it when a search actually returns the answer I'm looking for. Woohoo!