PostgreSQL Update Column From Lookup
You don't have a correct syntax.

UPDATE table1 set user_id = table2.user_id from table2 where table1.pid = table2.pid


You can also update data from the same table, just join the same table with a new alias

UPDATE table1 set user_id = (table2.json-&gt;&gt;'user_id')::bigint from table1 as table2 where table1.pid = table2.pid