Thread

Update using sub-select table in schema

Chris Dunlop <chris@onthe.net.au> — 2006-10-02T02:19:50Z

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
);
----------------------------------------------------------------------