Re: Subselects to populate a table

On Sat, 2003-01-04 at 02:25, Chris Boget wrote:
> I've read through the docs and I couldn't find many examples of using
> subselects.  I could find out and read all about what they were, but not
> many examples.  If someone could point to a page that does show this,
> I'd be ever so appreciative!
> Anyways, I'm redoing a table I imported from MySQL (as part of my
> conversion to PG from MySQL) and I'm having problems with this.  The
> error I'm getting is that it says you can't select more than one column.
> Why?  Is there another way to do what I'm trying?  Here is my query:
>
> INSERT INTO "cards_type_temp"
> ( "card_game", "card_name", "card_table", "record_num" )
> VALUES
> ( 'Vtes', ( SELECT "card_name", "card_type", "record_num" FROM "cards_type" ));
>
> Any help or insight would be great!

INSERT INTO "cards_type_temp" ( "card_game", "card_name", "card_table",
"record_num" ) ( SELECT 'Vtes', "card_name", "card_type", "record_num"
FROM "cards_type" );

i.e. You want to move the constant 'vtes' into the sub-select and get
rid of the VALUES( ) around the subselect.

That's probably the most efficient way anyway, but normally there would
be a WHERE ... clause on that subselect so that you don't get all rows
from cards_type inserted.  Maybe you want that in this case.

Cheers,
                    Andrew.
--
---------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/         PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201     MOB: +64(21)635-694    OFFICE: +64(4)499-2267
           Survey for nothing with http://survey.net.nz/
---------------------------------------------------------------------