Thread: 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

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