Re: 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(dot)smith(at)weatherbeeta(dot)com(dot)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(dot)rocha(at)gmail(dot)com]
> *Sent:* Monday, 26 February 2007 00:43
> *To:* phillip(dot)smith(at)weatherbeeta(dot)com(dot)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/