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

Herouth Maoz wrote:

INSERT INTO BOOK (name, author)
SELECT 'War and Peace', oid
FROM author
WHERE name = 'Tolstoy';

Thanks! Now what if I had...
CREATE TABLE book (author oid, publisher oid);

What is the syntax? Is it...

INSERT INTO BOOK(author, publisher)
SELECT author.oid, publisher.oid
FROM author, publisher
WHERE author.name = 'Tolstoy' AND publisher.name = 'Penguin';

Is that right?

--
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com

At 17:03 +0200 on 30/03/1999, Chris Bitmead wrote:

Thanks! Now what if I had...
CREATE TABLE book (author oid, publisher oid);

What is the syntax? Is it...

INSERT INTO BOOK(author, publisher)
SELECT author.oid, publisher.oid
FROM author, publisher
WHERE author.name = 'Tolstoy' AND publisher.name = 'Penguin';

Is that right?

Looks right to me.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

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              |
+-------------------------+--------------------------------------+