pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to do this in SQL?
5+ messages / 3 participants
[nested] [flat]
* How to do this in SQL?
@ 1999-03-30 14:45 Chris Bitmead <chris.bitmead@bigfoot.com>
0 siblings, 2 replies; 5+ messages in thread
From: Chris Bitmead @ 1999-03-30 14:45 UTC (permalink / raw)
To: 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?
--
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [GENERAL] How to do this in SQL?
@ 1999-03-30 14:59 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Chris Bitmead <chris.bitmead@bigfoot.com>
1 sibling, 2 replies; 5+ messages in thread
From: Herouth Maoz @ 1999-03-30 14:59 UTC (permalink / raw)
To: Chris Bitmead <chris.bitmead@bigfoot.com>; pgsql-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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [GENERAL] How to do this in SQL?
@ 1999-03-30 15:03 Chris Bitmead <chris.bitmead@bigfoot.com>
parent: Herouth Maoz <herouth@oumail.openu.ac.il>
1 sibling, 0 replies; 5+ messages in thread
From: Chris Bitmead @ 1999-03-30 15:03 UTC (permalink / raw)
To: Herouth Maoz <herouth@oumail.openu.ac.il>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [GENERAL] How to do this in SQL?
@ 1999-03-30 15:13 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Herouth Maoz <herouth@oumail.openu.ac.il>
1 sibling, 0 replies; 5+ messages in thread
From: Herouth Maoz @ 1999-03-30 15:13 UTC (permalink / raw)
To: Chris Bitmead <chris.bitmead@bigfoot.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [GENERAL] How to do this in SQL?
@ 1999-03-30 15:50 Stuart Rison <stuart@ludwig.ucl.ac.uk>
parent: Chris Bitmead <chris.bitmead@bigfoot.com>
1 sibling, 0 replies; 5+ messages in thread
From: Stuart Rison @ 1999-03-30 15:50 UTC (permalink / raw)
To: Chris Bitmead <chris.bitmead@bigfoot.com>; +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 |
+-------------------------+--------------------------------------+
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~1999-03-30 15:50 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-03-30 14:45 How to do this in SQL? Chris Bitmead <chris.bitmead@bigfoot.com>
1999-03-30 14:59 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-03-30 15:03 ` Chris Bitmead <chris.bitmead@bigfoot.com>
1999-03-30 15:13 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-03-30 15:50 ` Stuart Rison <stuart@ludwig.ucl.ac.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox