Re: Insert based in a select - Mailing list pgsql-sql

<div class="Section1"><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size:
10.0pt;font-family:Arial;color:navy">(Please reply to the list when replying)</span></font><p class="MsoNormal"><font
color="navy"face="Arial" size="2"><span style="font-size: 
10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:
10.0pt;font-family:Arial;color:navy">The error is correct – you are telling PG to insert one row (the literal values
you’vepassed to INSERT), but the sub-query is returning multiple rows which won’t fit in a single row.</span></font><p
class="MsoNormal"><fontcolor="navy" face="Arial" size="2"><span style="font-size: 
10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:
10.0pt;font-family:Arial;color:navy">I see 2 options, but someone else I’m sure will have a more elegant way to do
it:</span></font><pclass="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 
10.0pt;font-family:Arial;color:navy">1) insert into base.ingresso (select id from base.cartao order by
id)</span></font><pclass="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 
10.0pt;font-family:Arial;color:navy">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)</span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 
10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:
10.0pt;font-family:Arial;color:navy">Number 1 will create one row in base.ingresso for each row in base.cartao. You
willthen need to use UPDATE to adjust the values in each of those rows to the other values you want.</span></font><p
class="MsoNormal"><fontcolor="navy" face="Arial" size="2"><span style="font-size: 
10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:
10.0pt;font-family:Arial;color:navy">Number 2 would need to be run over and over again – not a practical option I
imagine…</span></font><pclass="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 
10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:
10.0pt;font-family:Arial;color:navy">~p</span></font><p class="MsoNormal"><font color="navy" face="Arial"
size="2"><spanstyle="font-size: 
10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal" style="margin-left:36.0pt"><font face="Tahoma"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:Tahoma">-----Original Message-----<br /><b><span
style="font-weight:bold">From:</span></b>Ezequias Rodrigues da Rocha [mailto:ezequias.rocha@gmail.com] <br /><b><span
style="font-weight:bold">Sent:</span></b></span></font><font face="Tahoma" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Tahoma">Monday,26 February 2007</span></font><font face="Tahoma" size="2"><span
lang="EN-US"style="font-size:10.0pt;font-family:Tahoma"> </span></font><font face="Tahoma" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Tahoma">00:43</span></font><fontface="Tahoma" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Tahoma"><br/><b><span style="font-weight:bold">To:</span></b>
phillip.smith@weatherbeeta.com.au<br/><b><span style="font-weight:bold">Subject:</span></b> Re: [SQL] Insert based in a
select</span></font><pclass="MsoNormal" style="margin-left:36.0pt"><font face="Times New Roman" size="3"><span
style="font-size:12.0pt"> </span></font><pclass="MsoNormal" style="margin-left:36.0pt"><font face="Times New Roman"
size="3"><spanstyle="font-size:12.0pt">Phillip,<br /><br /> Thank you for the information but the master (id) is only a
fieldof my secondary table.<br /><br /> My sql statement is like this:<br /><br /></span></font><b><font size="1"><span
style="font-size:7.5pt;font-weight:bold">insertinto 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)</span></font></b><br /><br /> And I am
gettingthe following error return message:<br /><br /><b><span style="font-weight:bold">ERROR: more than one row
returnedby a subquery used as an expression <br /> SQL state: 21000<br /></span></b><br /> Could you give me another
helpwith this ?<br /><br /> Ezequias</div><br /><p><b>*******************Confidentiality and Privilege
Notice*******************</b><p>The material contained in this message is privileged and confidential to the addressee.
Ifyou are not the addressee indicated in this message or responsible for delivery of the message to such person, you
maynot copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email.
<p>Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither
givennor 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 <br />