Re: Subquery in INSERT?

Wilfred Benson wrote:
> What I'm trying to do is copy a value from one column to another in the
> same table. The table looks like this:
>
>  first | second
> ---------------------
> 1      |
> 2      |
> 3      |
> 4      |
> ...
>
> It's named 'copier'.
>
> The first column is sequence-generated, and the second has yet to have
> anything loaded (at this stage it's only a test table). So what I'm
> trying to do is quick copy of '1' from 'first' to 'second' with this query:
>
> INSERT INTO copier VALUES(nextval('sequence'), ((SELECT first FROM
> copier WHERE copier.first=1) AS second));
>
> ...and I'm getting this error:
> ERROR: syntax error at or near "AS" at character 93

try -

INSERT INTO copier VALUES(nextval('sequence'), (SELECT first FROM
copier WHERE copier.first=1));

You don't need to specify that the second value is column 'second' (the
'AS second' part that you entered).

you will probably want to change WHERE copier.first=1 to something like
WHERE copier.first=random()*100 otherwise you will always get 1 in the
second column.

Also if you change your table to be

CREATE TABLE copier
(
   "first" serial PRIMARY KEY,
   "second" integer
);

you can then have
INSERT INTO copier (second) VALUES ( (SELECT first FROM copier WHERE
copier.first=random()*100) );

The serial type will implicitly create DEFAULT nextval('sequence') so if
you don't give it a value (which makes the value NULL) it will insert
the sequence number for you, and the PRIMARY KEY (good practice) will
also create an index on it which will speed things up if you are
planning to test with thousands of entries.

You'll probably end up with something like -
INSERT INTO copier (second) VALUES ( (SELECT first FROM copier WHERE
copier.first=round(random()*(SELECT max(first) FROM copier))+1) );

Then simplify it to
INSERT INTO copier (second) VALUES (round(random()*(SELECT max(first)
FROM copier))+1 );


--

Shane Ambler
Postgres@007Marketing.com

Get Sheeky @ http://Sheeky.Biz