quirk with update a from b

Started by mike gabout 20 years ago2 messagesgeneral
Jump to latest
#1mike g
mike@thegodshalls.com

CREATE TABLE aa
(
a_col1 text
);

CREATE TABLE bb
(
b_col1 text
);

This update works:
UPDATE bb
SET b_col1 = aa.a_col1
FROM aa
WHERE bb.b_col1 <> aa.a_col1;

This does not:
UPDATE bb
SET bb.b_col1 = aa.a_col1
FROM aa
WHERE bb.b_col1 <> aa.a_col1;

Error: column "bb" of relation "bb" does not exist.

postgres 8.1.2

Maybe in a future version the alias can be allowed? Perhaps someone wanted to avoid updating the wrong column on accident and put this in as a safety net?

Mike

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: mike g (#1)
Re: quirk with update a from b

"Mike G." <mike@thegodshalls.com> writes:

UPDATE bb
SET bb.b_col1 = aa.a_col1
FROM aa
WHERE bb.b_col1 <> aa.a_col1;

Error: column "bb" of relation "bb" does not exist.

Maybe in a future version the alias can be allowed?

No. It's contrary to SQL spec, and if we allowed it we'd have an
ambiguity: are you assigning to field b_col1 of bb, or assigning
to field b_col1 of a composite-type field named bb in table bb?

regards, tom lane