Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 88B4A9FB441 for ; Sun, 25 Feb 2007 18:17:49 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 18063-10 for ; Sun, 25 Feb 2007 18:17:40 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from weatherbeeta.com.au (218-214-212-153.people.net.au [218.214.212.153]) by postgresql.org (Postfix) with SMTP id E92C69FB39B for ; Sun, 25 Feb 2007 18:17:42 -0400 (AST) Received: (qmail 8824 invoked from network); 25 Feb 2007 22:17:41 -0000 X-NetworkBox-HamSign: 0101;OUT;wbeetaau1;86e6e70d73c3aa5341a4d114d780aa92; Received: from mail.weatherbeeta.com.au (10.10.0.2) by network-box.weatherbeeta.com.au with SMTP; 25 Feb 2007 22:17:41 -0000 Received: (qmail 26398 invoked by uid 1166); 26 Feb 2007 09:17:39 +1100 Received: from 172.20.0.155 by mail.weatherbeeta.com.au (envelope-from , uid 1165) with qmail-scanner-1.25-st-qms (clamdscan: 0.88.6/2315. perlscan: 1.25-st-qms. Clear:RC:1(172.20.0.155):. Processed in 1.841207 secs); 25 Feb 2007 22:17:39 -0000 X-Antivirus-mail.weatherbeeta.com.au-Mail-From: phillip.smith@weatherbeeta.com.au via mail.weatherbeeta.com.au X-Antivirus-mail.weatherbeeta.com.au: 1.25-st-qms (Clear:RC:1(172.20.0.155):. Processed in 1.841207 secs Process 26374) Received: from it-phil.weatherbeeta.com.au (HELO wbaus090) (172.20.0.155) by mail.weatherbeeta.com.au with SMTP; 26 Feb 2007 09:17:35 +1100 From: "Phillip Smith" To: "'Ezequias Rodrigues da Rocha'" Cc: Subject: Re: Insert based in a select Date: Mon, 26 Feb 2007 09:17:34 +1100 Organization: Weatherbeeta Pty Ptd Message-ID: <008301c7592a$bfed31c0$9b0014ac@wbaus090> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0084_01C75986.F35DA9C0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 Importance: Normal In-Reply-To: <55c095e90702250543w6bd52882yafe24498959d0788@mail.gmail.com> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 X-Scanned-By-wbeetaau1: Virus scan performed by network-box X-Scanned-By-wbeetaau1: Scanner file id is wbeetaau1-1172441861.39-8817-000 X-Scanned-By-wbeetaau1: No known viruses found in message (received+scanned in 0.06/0.14 secs) X-Scanned-By-wbeetaau1: Spam-Check-Result: No, hits=0 required=7 tests= autolearn=no version=2.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/258 X-Sequence-Number: 27592 This is a multi-part message in MIME format. ------=_NextPart_000_0084_01C75986.F35DA9C0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable (Please reply to the list when replying) =20 The error is correct - you are telling PG to insert one row (the literal values you've passed to INSERT), but the sub-query is returning multiple rows which won't fit in a single row. =20 I see 2 options, but someone else I'm sure will have a more elegant way = to do it: 1) insert into base.ingresso (select id from base.cartao order by id) 2) insert into base.ingresso values (nextval(' base.ingresso_id'), 4, = now(), 12.34, 12.34, 1, 1678, (select id from base.cartao where id not in = (SELECT id from base.ingresso) order by id limit 1), 2, 25, 99) =20 Number 1 will create one row in base.ingresso for each row in = base.cartao. You will then need to use UPDATE to adjust the values in each of those = rows to the other values you want. =20 Number 2 would need to be run over and over again - not a practical = option I imagine. =20 ~p =20 -----Original Message----- From: Ezequias Rodrigues da Rocha [mailto:ezequias.rocha@gmail.com]=20 Sent: Monday, 26 February 2007 00:43 To: phillip.smith@weatherbeeta.com.au Subject: Re: [SQL] Insert based in a select =20 Phillip, Thank you for the information but the master (id) is only a field of my secondary table. My sql statement is like this: insert into base.ingresso values (nextval(' base.ingresso_id'), 4, = now(), 12.34, 12.34, 1, 1678, (select id from base.cartao order by id), 2, 25, = 99) And I am getting the following error return message: ERROR: more than one row returned by a subquery used as an expression=20 SQL state: 21000 Could you give me another help with this ? Ezequias =0A*******************Confidentiality and Privilege Notice*******************= =0A=0AThe material contained in this message is privileged and confidential to= =0Athe addressee. If you are not the addressee indicated in this message or= =0Aresponsible for delivery of the message to such person, you may not copy=0Aor= deliver this message to anyone, and you should destroy it and kindly=0Anotify= the sender by reply email.=0A=0AInformation in this message that does not relat= e to the official business=0Aof Weatherbeeta must be treated as neither given = nor endorsed by Weatherbeeta.=0AWeatherbeeta, its employees, contractors or as= sociates shall not be liable=0Afor direct, indirect or consequential loss aris= ing from transmission of this=0Amessage or any attachments=0A ------=_NextPart_000_0084_01C75986.F35DA9C0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

(Please reply to the list when = replying)

 

The error is correct – you = are telling PG to insert one row (the literal values you’ve passed to INSERT), but the sub-query is returning multiple rows which won’t = fit in a single row.

 

I see 2 options, but someone else = I’m sure will have a more elegant way to do it:

1) insert into base.ingresso = (select id from base.cartao order by id)

2) insert into base.ingresso values = (nextval(' base.ingresso_id'), 4, now(), 12.34, 12.34, 1, 1678, (select id from = base.cartao where id not in (SELECT id from base.ingresso) order by id limit 1), 2, = 25, 99)

 

Number 1 will create one row in = base.ingresso for each row in base.cartao. You will then need to use UPDATE to adjust = the values in each of those rows to the other values you = want.

 

Number 2 would need to be run over = and over again – not a practical option I = imagine…

 

~p

 

-----Original Message-----
From: Ezequias Rodrigues = da Rocha [mailto:ezequias.rocha@gmail.com]
Sent: Monday, 26 February 2007 00:43
To: phillip.smith@weatherbeeta.com.au
Subject: Re: [SQL] Insert = based in a select

 

Phillip,

Thank you for the information but the master (id) is only a field of my secondary table.

My sql statement is like this:

insert into base.ingresso values (nextval(' base.ingresso_id'), 4, now(), = 12.34, 12.34, 1, 1678, (select id from base.cartao order by id), 2, 25, = 99)

And I am getting the following error return message:

ERROR: more than one row returned by = a subquery used as an expression
SQL state: 21000

Could you give me another help with this ?

Ezequias


=

=0A*******************Confidentiality and Privilege Notice*************= ******=0A

=0AThe material contained in this message is privileged and conf= idential to=0Athe addressee. If you are not the addressee indicated in this m= essage or=0Aresponsible for delivery of the message to such person, you may no= t copy=0Aor deliver this message to anyone, and you should destroy it and kind= ly=0Anotify the sender by reply email.=0A

=0AInformation in this message that do= es not relate to the official business=0Aof Weatherbeeta must be treated as ne= ither given nor endorsed by Weatherbeeta.=0AWeatherbeeta, its employees, contr= actors or associates shall not be liable=0Afor direct, indirect or consequenti= al loss arising from transmission of this=0Amessage or any attachments=0A
= ------=_NextPart_000_0084_01C75986.F35DA9C0--