5
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?

Mariano D'Ascanio
  • 1,202
  • 2
  • 16
  • 17
user3579301
  • 53
  • 1
  • 1
  • 3
  • Is it MySQL or Postgresql? – Clodoaldo Neto Jul 24 '14 at 17:17
  • 1
    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 Jul 24 '14 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 Jul 24 '14 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 Jul 24 '14 at 22:23
  • Possible duplicate of [Postgres won't accept table alias before column name](http://stackoverflow.com/questions/11369757/postgres-wont-accept-table-alias-before-column-name) – Evan Carroll Dec 19 '16 at 23:53

1 Answers1

7

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

Rahul
  • 76,197
  • 13
  • 71
  • 125