Update table A by Joining Table B and Table A

I have table A, Table B My database looks some thing like this Table A key1 key2 key5 key6

Table B key3 key4

Data in A.key2 is same as data in B.key3 and A.key5 has same data as B.key 4 I want to update key1 in Table A to another row in table A based on some condition on key6 of table A

I want to update some rows in Table A with Values which I would get by joining Table A with Table B.

I tried running a query like

UPDATE table A set a.key1 = c.key1 FROM A a JOIN B b ON a.key2 = b.key3 JOIN A c ON b.key4 = c.key5 WHERE A.key6 = somevalue; 

sql throws an error – column a of relation A does not exist

Answer

This is the correct syntax for Postgresql:

UPDATE A a
SET key1 = c.key1 
FROM B b 
JOIN A c ON b.key4 = c.key5 
WHERE a.key2 = b.key3  AND a.key6 = somevalue;