Thread: Subqueries in Non-SELECT Queries

I am trying to do something that looks to me exactly like what's described in chapter 8.4
in the Postgresql book:

test=> UPDATE salesorder
test-> SET ship_date = '11/16/96'
test-> WHERE customer_id = (
test(> SELECT customer_id
test(> FROM customer
test(> WHERE name = 'Fleer Gearworks, Inc.'
test(> );
UPDATE 1

Here only one row is updated. This should work with more than one, right? I get an error
which suggests that it doesn't:

mpi=# update index set level = 2 where parentid = ( select id from index where level = 1
);
ERROR:  More than one tuple returned by a subselect used as an expression.
mpi=#

This is a recursive query, on a single table. Is that not possible?

I am using 7.1.

- Frank

Frank Joerdens wrote:

> mpi=# update index set level = 2 where parentid = ( select id from
> index where level = 1 );
> ERROR:  More than one tuple returned by a subselect used as an
> expression. mpi=#
>
> This is a recursive query, on a single table. Is that not possible?

Hi Frank,

as the error message says, the subselect must return only one value,
since the where clause compares just one field for equality. You might 
try IN instead of =, ie

update index set level = 2 where parentid in ( select id from
index where level = 1 );

Hope that helps.

Christof
--          gl.aser . software engineering . internet service      http://gl.aser.de/  . Planckstraße 7 . D-39104
Magdeburg
Tel. +49.391.7 44 77 10 . Fax +49.391.7 44 77 13 . Mobil 0177.77 92 84 3

Frank Joerdens <frank@joerdens.de> writes:
> mpi=# update index set level = 2 where parentid = ( select id from
> index where level = 1 );
> ERROR:  More than one tuple returned by a subselect used as an expression.

Apparently the subquery "select id from index where level = 1" is
returning more than one row.  Perhaps you want WHERE parentid IN subselect
rather than WHERE parentid = subselect.  "=" is a scalar operator, not
a set operator.
        regards, tom lane

Christof Glaser wrote:
[ . . . ]
> since the where clause compares just one field for equality. You might
> try IN instead of =, ie
> 
> update index set level = 2 where parentid in ( select id from
> index where level = 1 );

That was it! Thanks, Frank