pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Subqueries in Non-SELECT Queries
4+ messages / 3 participants
[nested] [flat]

* Subqueries in Non-SELECT Queries
@ 2000-12-17 18:03 Frank Joerdens <frank@joerdens.de>
  2000-12-17 18:32 ` Re: Subqueries in Non-SELECT Queries Christof Glaser <gcg@gl.aser.de>
  2000-12-17 18:43 ` Re: Subqueries in Non-SELECT Queries Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 4+ messages in thread

From: Frank Joerdens @ 2000-12-17 18:03 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Subqueries in Non-SELECT Queries
  2000-12-17 18:03 Subqueries in Non-SELECT Queries Frank Joerdens <frank@joerdens.de>
@ 2000-12-17 18:32 ` Christof Glaser <gcg@gl.aser.de>
  2000-12-18 10:43   ` Re: Subqueries in Non-SELECT Queries Frank Joerdens <frank@joerdens.de>
  1 sibling, 1 reply; 4+ messages in thread

From: Christof Glaser @ 2000-12-17 18:32 UTC (permalink / raw)
  To: Frank Joerdens <frank@joerdens.de>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Subqueries in Non-SELECT Queries
  2000-12-17 18:03 Subqueries in Non-SELECT Queries Frank Joerdens <frank@joerdens.de>
  2000-12-17 18:32 ` Re: Subqueries in Non-SELECT Queries Christof Glaser <gcg@gl.aser.de>
@ 2000-12-18 10:43   ` Frank Joerdens <frank@joerdens.de>
  0 siblings, 0 replies; 4+ messages in thread

From: Frank Joerdens @ 2000-12-18 10:43 UTC (permalink / raw)
  To: Christof Glaser <gcg@gl.aser.de>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Subqueries in Non-SELECT Queries
  2000-12-17 18:03 Subqueries in Non-SELECT Queries Frank Joerdens <frank@joerdens.de>
@ 2000-12-17 18:43 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 4+ messages in thread

From: Tom Lane @ 2000-12-17 18:43 UTC (permalink / raw)
  To: Frank Joerdens <frank@joerdens.de>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2000-12-18 10:43 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-12-17 18:03 Subqueries in Non-SELECT Queries Frank Joerdens <frank@joerdens.de>
2000-12-17 18:32 ` Christof Glaser <gcg@gl.aser.de>
2000-12-18 10:43   ` Frank Joerdens <frank@joerdens.de>
2000-12-17 18:43 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox