G'day all,
PG version: 8.1.4 (also 7.4.13)
OS: Linux (debian/testing)
In an update, using a sub-select from a table with the same name
but in a different schema results in an error. The same thing
works if you alias the sub-select table or if you qualify the
schema of the update table.
See script below.
I'm not sure if this is a bug or if it's displaying my ignorance
of this corner of SQL...
Cheers,
Chris.
----------------------------------------------------------------------
create table a (id integer, name text);
insert into a values ('1', 'thomas');
insert into a values ('2', 'edward');
create schema temp;
create table temp.a (id integer, name text);
insert into temp.a values ('1', 'tom');
insert into temp.a values ('2', 'eddie');
--
-- fails with:
-- ERROR: more than one row returned by a subquery used as an expression
--
update a set name = (
select name
from temp.a
where temp.a.id = a.id
)
--
-- same thing with schema-qualified update table: works as expected
--
update public.a set name = (
select name
from temp.a
where temp.a.id = public.a.id
);
--
-- same thing but using an alias: works as expected
--
update a set name = (
select name
from temp.a foo
where foo.id = a.id
);
----------------------------------------------------------------------
