Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 82CA89FB3AA for ; Sun, 25 Feb 2007 20:20:23 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 47270-02 for ; Sun, 25 Feb 2007 20:20:12 -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 42EF29FB3A5 for ; Sun, 25 Feb 2007 20:20:14 -0400 (AST) Received: (qmail 19198 invoked from network); 26 Feb 2007 00:20:13 -0000 X-NetworkBox-HamSign: 0101;OUT;wbeetaau1;e81376253404ed547b99748761d021b5; Received: from mail.weatherbeeta.com.au (10.10.0.2) by network-box.weatherbeeta.com.au with SMTP; 26 Feb 2007 00:20:13 -0000 Received: (qmail 15800 invoked by uid 1166); 26 Feb 2007 11:20:13 +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 3.638346 secs); 26 Feb 2007 00:20:13 -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 3.638346 secs Process 15769) Received: from it-phil.weatherbeeta.com.au (HELO wbaus090) (172.20.0.155) by mail.weatherbeeta.com.au with SMTP; 26 Feb 2007 11:20:06 +1100 From: "Phillip Smith" To: "'Ezequias Rodrigues da Rocha'" Cc: Subject: Re: Insert based in a select Date: Mon, 26 Feb 2007 11:20:06 +1100 Organization: Weatherbeeta Pty Ptd Message-ID: <00ce01c7593b$ddc27640$9b0014ac@wbaus090> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00CF_01C75998.1132EE40" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 Importance: Normal In-Reply-To: <55c095e90702251557v3cc96b10m10b28c98ca8c89ee@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-1172449213.166-19193-000 X-Scanned-By-wbeetaau1: No known viruses found in message (received+scanned in 0.08/0.19 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-Spam-Status: No, hits=0.042 tagged_above=0 required=5 tests=AWL, BAYES_50, FORGED_RCVD_HELO, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200702/260 X-Sequence-Number: 27594 This is a multi-part message in MIME format. ------=_NextPart_000_00CF_01C75998.1132EE40 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Apologies =96 the first statement should have included the column name = to insert to: INSERT INTO base.ingresso (id_column) (SELECT id FROM base.cartao ORDER = BY id) =20 That will insert one row in ingresso for each row in cartao =96 only = changing the id column. All the other columns in each row will be populated with = the default values. That is why you will need to do an UPDATE afterwards. Something like: UPDATE base.ingresso SET col1 =3D =91value1=92, sol2 =3D =91value2=92 WHERE id IN (SELECT id FROM base.cartao); =20 Hope this makes sense, ~p =20 =20 -----Original Message----- From: pgsql-sql-owner@postgresql.org = [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Ezequias Rodrigues da Rocha Sent: Monday, 26 February 2007 10:58 To: Phillip Smith Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Insert based in a select =20 The second statement works but only 1 row was affected. It do not apply = to me. The first statement i didn't understand. You are adding only the first = field on my base.ingresso table ? Regards Ezequias 2007/2/25, Phillip Smith : (Please reply to the list when replying) =20 The error is correct =96 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 =96 not a practical = option I imagine=85 =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 =20 *******************Confidentiality and Privilege = Notice*******************=20 The material contained in this message is privileged and confidential to = the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy = or deliver this message to anyone, and you should destroy it and kindly = notify the sender by reply email.=20 Information in this message that does not relate to the official = business of Weatherbeeta must be treated as neither given nor endorsed by = Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be = liable for direct, indirect or consequential loss arising from transmission of = this message or any attachments=20 --=20 =3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D= -=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha=20 =3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D= -=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D- A pior das democracias ainda =E9 melhor do que a melhor das ditaduras The worst of democracies is still better than the better of = dictatorships=20 http://ezequiasrocha.blogspot.com/=20 =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_00CF_01C75998.1132EE40 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Apologies – the first = statement should have included the column name to insert to:

INSERT INTO base.ingresso (id_column) (SELECT id FROM base.cartao ORDER BY = id)

 

That will insert one row in = ingresso for each row in cartao – only changing the id column. All the other = columns in each row will be populated with the default values. That is why you will need = to do an UPDATE afterwards. Something like:

UPDATE = base.ingresso

SET col1 =3D ‘value1’, sol2 =3D = ‘value2’

WHERE id IN (SELECT id FROM base.cartao);

 

Hope this makes = sense,

~p

 

 

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] = On Behalf Of Ezequias Rodrigues da = Rocha
Sent: Monday, 26 February 2007 10:58
To: Phillip Smith
Cc: = pgsql-sql@postgresql.org
Subject: Re: [SQL] Insert = based in a select

 

The second statement works but only 1 row was affected. It do not apply to = me.

The first statement i didn't understand. You are adding only the first = field on my base.ingresso table ?

Regards
Ezequias

2007/2/25, Phillip Smith <phillip.smith@weatherbe= eta.com.au>:

(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<= /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<= /a>
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

 

*******************Confidenti= ality and Privilege Notice*******************

The material contained in this message is = privileged and confidential to the addressee. If you are not the addressee = indicated in this message or responsible for delivery of the message to such person, = you may not copy or deliver this message to anyone, and you should destroy it = and kindly notify the sender by reply email.

Information in this message that does not = relate to the official business of Weatherbeeta must be treated as neither given = nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential = loss arising from transmission of this message or any attachments =




--
=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D= -=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D
            &= nbsp;           &n= bsp;         Atenciosamente (Sincerely)
            &= nbsp;           Ez= equias Rodrigues da Rocha
=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D= -=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-
= A pior das democracias ainda =E9 melhor do que a melhor das = ditaduras
The worst of democracies is still better than the better of = dictatorships
http://ezequiasrocha.blogspot= .com/


=

=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_00CF_01C75998.1132EE40--