Thread: Another nut to crack with insertion into tables... *sigh*
Another nut to crack with insertion into tables... *sigh*
From
"Gemeinschaft Studienarbeit Datenbanken"
Date:
insert into Rollenspiel (Name, HerstellerID, Erscheinungsdatum, Bestellnummer, Preisempfehlung, Beschreibung, Computertyp, Datentraegertyp, System, AnzahlCharaktere, Typ, Cluebook, SpielstandEditor) values ('Might & Magic I', (select HerstellerNummer from Hersteller where Name like '%Blueborg%'), '17.03.1986', 'role01', '29,50', '{"Einfach irgendeine Beschreibung halt..."}', '{"PC", "Mac", "c64", "Atari"}', '{"Diskette", "CD"}', 'Freies System', 6, 'Fantasy', '{"Solution to Might & Magic Series", "The Might & Magic I Solution"}', '{"http://www.mightandmagic.de/I/editors.html"}'); ERROR: parser: parse error at or near "select" The question is: why? spiele=> select HerstellerNummer from Hersteller; herstellernummer ---------------- 1 2 30 31 32 4 5 6 7 8 66 70 99 17 88 (15 rows) Doing this from psql plainly yields the wanted result: spiele=> select HerstellerNummer from Hersteller where Name like '%Blueborg%'; herstellernummer ---------------- 7 (1 row) Pointers to FAQs, solutions, help, donuts greatly appreciated ;-)
Hello Gemeinschaft, domenica, 27 settembre 98, you wrote: GSD> insert into Rollenspiel GSD> (Name, HerstellerID, Erscheinungsdatum, GSD> Bestellnummer, Preisempfehlung, GSD> Beschreibung, GSD> Computertyp, Datentraegertyp, GSD> System, AnzahlCharaktere, Typ, GSD> Cluebook, SpielstandEditor) GSD> values GSD> ('Might & Magic I', (select HerstellerNummer from Hersteller GSD> where Name like '%Blueborg%'), '17.03.1986', GSD> 'role01', '29,50', GSD> '{"Einfach irgendeine Beschreibung halt..."}', GSD> '{"PC", "Mac", "c64", "Atari"}', '{"Diskette", "CD"}', GSD> 'Freies System', 6, 'Fantasy', GSD> '{"Solution to Might & Magic Series", "The Might & Magic I Solution"}', GSD> '{"http://www.mightandmagic.de/I/editors.html"}'); GSD> ERROR: parser: parse error at or near "select" GSD> The question is: why? Actually, you can't assign the result of a subquery to a column, This is a TODO item: * Allow subqueries in target list Jose'
I dunno about arrays, but this one I think I can answer: > insert into Rollenspiel > (Name, HerstellerID, Erscheinungsdatum, > Bestellnummer, Preisempfehlung, > Beschreibung, > Computertyp, Datentraegertyp, > System, AnzahlCharaktere, Typ, > Cluebook, SpielstandEditor) > values > ('Might & Magic I', (select HerstellerNummer from Hersteller > where Name like '%Blueborg%'), '17.03.1986', > 'role01', '29,50', > '{"Einfach irgendeine Beschreibung halt..."}', > '{"PC", "Mac", "c64", "Atari"}', '{"Diskette", "CD"}', > 'Freies System', 6, 'Fantasy', > '{"Solution to Might & Magic Series", "The Might & Magic I Solution"}', > '{"http://www.mightandmagic.de/I/editors.html"}'); > ERROR: parser: parse error at or near "select" I believe sub-selects are currently only supported in WHERE clauses. Supporting them elsewhere is on the TODO list for a future release (no, 6.4 won't have it). In the meantime you'll have to read back the result of the sub-select and include it in the text of the INSERT. (I'm not sure whether the SQL spec allows a select in an INSERT like this anyway. What happens if the sub-select returns no tuples, or more than one tuple?) regards, tom lane
At 16:02 +0200 on 28/9/98, Tom Lane wrote: > > I believe sub-selects are currently only supported in WHERE clauses. > Supporting them elsewhere is on the TODO list for a future release > (no, 6.4 won't have it). In the meantime you'll have to read back > the result of the sub-select and include it in the text of the INSERT. > > (I'm not sure whether the SQL spec allows a select in an INSERT like this > anyway. What happens if the sub-select returns no tuples, or more than > one tuple?) First, there is an exception which is supposed to be thrown in such a case. But anyway, there is no reason to do things like this. Read the "insert" manpage. The syntax is: insert into classname [(att.expr-1 [,att_expr.i] )] {values (expression1 [,expression-i] ) | select expression1 [,expression-i] [from from-list] [where qual] So, you should format your insert like this: INSERT INTO my_table (int_field, text_field, another_int_field, another_text_field ) SELECT 15, 'The Wind in the Willows', foreign_int_field, foreign_text_field FROM foreign_table WHERE ... Everything which you want inserted literally, you put as constants on the select list. It's a valid expression. Anything you want from the other table (or tables!), you mention by its field name. Very simple, and has been working for ages. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma