Received: from mail-b.bcc.ac.uk (mail-b.bcc.ac.uk [144.82.100.22]) by hub.org (8.9.2/8.9.1) with ESMTP id KAA55379 for ; Tue, 30 Mar 1999 10:53:03 -0500 (EST) (envelope-from stuart@ludwig.ucl.ac.uk) Received: from kestrel.ludwig.ucl.ac.uk by mail-b.bcc.ac.uk with SMTP (XT-PP); Tue, 30 Mar 1999 16:50:15 +0100 Received: from [128.40.242.190] by kestrel.ludwig.ucl.ac.uk (SMI-8.6/SMI-SVR4) id QAA03429; Tue, 30 Mar 1999 16:53:18 +0100 X-Sender: stuart@kestrel.ludwig.ucl.ac.uk Message-Id: In-Reply-To: <3700E388.3E3A77E1@bigfoot.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Tue, 30 Mar 1999 16:50:12 +0100 To: Chris Bitmead From: Stuart Rison Subject: Re: [GENERAL] How to do this in SQL? Cc: pgsql-general@postgreSQL.org >Let's say I had a table... >CREATE TABLE book ( > author oid, > name text ); >and... >CREATE TABLE author ( > name text ); > >and I wanted to create a book pointing to author with name 'Tolstoy'. I >want to do something like... > >INSERT INTO book(name,author) values('War and Peace', > (SELECT oid FROM author WHERE name = 'Tolstoy')); > >but this doesn't work. What is the correct syntax? > try \h insert in psql for a description of the correct syntax. Your query becomes: INSERT INTO book(author,name) SELECT oid,'War and Peace' FROM author WHERE name='Tolstoy'; Stuart. +-------------------------+--------------------------------------+ | Stuart Rison | Ludwig Institute for Cancer Research | +-------------------------+ 91 Riding House Street | | Tel. (0171) 878 4041 | London, W1P 8BT, UNITED KINGDOM. | | Fax. (0171) 878 4040 | stuart@ludwig.ucl.ac.uk | +-------------------------+--------------------------------------+