PostgreSQL: Error: column of relation does not exist

Question

UPDATE master as m
SET m.husband = p.id
From per as p
where m.drid = p.drid AND p.address > 80000 AND p.address <= 154969

I have a table called per which has column called id. Basically, I want to copy these ids in my another table called master on the where clause.

But I am getting an error saying, column "m" of relation "master" does not exist. m is not a column. I am not sure where I went wrong?

Answer 1

Try it like below rather; remove table alias m from SET operation and make it SET husband = p.id

UPDATE master m
SET husband = p.id
From per p
where m.drid = p.drid 
AND p.address > 80000 
AND p.address <= 154969

(OR) without using table alias at all like

UPDATE master 
SET husband = per.id
From per
where master.drid = per.drid 
AND per.address > 80000 
AND per.address <= 154969

See Postgres Documentation For More