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?

The error is related to the clause "as m". This is not possible in this case. Furthermore it's hard to understand, what you actually want to acomplish.Try this out: UPDATE master SET husband = (SELECT id FROM per WHERE master.drid = per.drid AND per.address > 80000 AND per.address <= 154969); — Jan Trienes
– Jan Trienes, Commented Jul 24, 2014 at 17:18
Jan Trienes, I am trying update column id in table per same as column husband of table master on condition where drids from the both the tables are same. Btw..your suggestion worked like I wanted. Thanks — user3579301
– user3579301, Commented Jul 24, 2014 at 17:25
@JanTrienes: You are definitely allowed to alias tables in an UPDATE. What you're not allowed to do is qualify target field names in SET clauses. — Nick Barnes
– Nick Barnes, Commented Jul 24, 2014 at 22:23
Possible duplicate of Postgres won't accept table alias before column name — Evan Carroll
– Evan Carroll, Commented Dec 19, 2016 at 23:53

Rahul · Accepted Answer · 2014-07-24 18:22:08Z

8

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

edited Jul 24, 2014 at 18:22

answered Jul 24, 2014 at 18:11

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: Error: column of relation does not exist

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related