SQL - Update a table using a field of another table

Question

I am trying to execute the following update query:

UPDATE Commodities 
INNER JOIN UniqueCountries 
     ON Commodities.wbCode = UniqueCountries.wbCode 
SET Idenb = UniqueCountries.wbName||yr

The query is clearly wrong, as it doesnt work. How can I fix it?

---

The query is supposed to update column IdenB with the concatenated value of wbName and yr (wbName||yr). However, wbName is on another table called UniqueCountries, which is why I tried to perform an Inner Join.

What am I doing wrong and how can I fix it? Thank you very much.

Answer 1

I can't see any indication in the docs that FROM or JOIN are supported in UPDATE statements.

Maybe you could try a correlated sub query if not.

UPDATE Commodities  
SET Idenb = (SELECT UniqueCountries.wbName||yr 
             FROM UniqueCountries 
             WHERE Commodities.wbCode = UniqueCountries.wbCode)