From: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
To: Phillip Smith <phillip.smith@weatherbeeta.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: Insert based in a select
Date: Sun, 25 Feb 2007 20:57:45 -0300
Message-ID: <55c095e90702251557v3cc96b10m10b28c98ca8c89ee@mail.gmail.com> (raw)
In-Reply-To: <008301c7592a$bfed31c0$9b0014ac@wbaus090>
References: <55c095e90702250543w6bd52882yafe24498959d0788@mail.gmail.com>
<008301c7592a$bfed31c0$9b0014ac@wbaus090>
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@weatherbeeta.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
>
>
>
> -----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
>
> ********************Confidentiality 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
>
>
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ezequias.rocha@gmail.com, phillip.smith@weatherbeeta.com.au
Subject: Re: Insert based in a select
In-Reply-To: <55c095e90702251557v3cc96b10m10b28c98ca8c89ee@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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