Received: from mx99.openu.ac.il (arbel.openu.ac.il [147.233.32.4]) by hub.org (8.9.2/8.9.1) with ESMTP id JAA47947 for ; Tue, 30 Mar 1999 09:57:39 -0500 (EST) (envelope-from herouth@oumail.openu.ac.il) Received: from talmon.openu.ac.il (talmon.openu.ac.il [147.233.32.11]) by mx99.openu.ac.il (Postfix) with ESMTP for id 12AA84B4E9; Tue, 30 Mar 1999 14:57:37 +0000 (Asia/Jerusalem) Received: from [147.233.148.140] (1164.pc.openu.ac.il) by talmon.openu.ac.il (Netscape Messaging Server 3.54) with ESMTP id AAA3BB0; Tue, 30 Mar 1999 16:59:23 +0200 X-Sender: herouth@shaked.cc.openu.ac.il 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:59:16 +0200 To: Chris Bitmead , pgsql-sql@postgreSQL.org From: Herouth Maoz Subject: Re: [GENERAL] How to do this in SQL? This question's topic is more appropriate to the SQL list than to the GENERAL list, so I am redirecting it there. At 16:45 +0200 on 30/03/1999, Chris Bitmead wrote: > > 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? This is not the correct syntax. The proper one is: INSERT INTO BOOK (name, author) SELECT 'War and Peace', oid FROM author WHERE name = 'Tolstoy'; This syntax appears in the documentation for the INSERT command, but most people disregard it, because they thing the SELECT statement must only select fields or expressions that involve fields. The truth is that the SELECT statement can select contstant-valued expressions. Perhaps an example in this spirit should be added to the documentation. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma