PostgreSQL Update Column From Lookup I'm trying to replace a column with a lookup from another table.  Seems simple, but I'm thinking PostgreSQL is getting in the way for some reason.  I've seen that some syntax that evidently doesn't work in PostgreSQL

UPDATE A
SET userid = (
SELECT pid FROM B WHERE B.pid = A.userid
)   # =&gt; You will need to rewrite or cast the expression (pointing to "(SELECT"


I think this SQL is valid, and I don't know what I am missing.  How can I get this column to do a simple 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