Thread: Subquery in INSERT?
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
So what I want to know is, is it possible? If it is and my query is
incorrect and anyone can tell me what's wrong with it that would be great.
Thanks a lot.
-Wilf.
P.S. Sorry if I posted this twice.
_________________________________________________________________
Stay in touch with old friends and meet new ones with Windows Live Spaces
http://clk.atdmt.com/MSN/go/msnnkwsp0070000001msn/direct/01/?href=http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.aspx&mkt=en-us
am Tue, dem 17.10.2006, um 5:06:59 -0700 mailte Wilfred Benson folgendes: > 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: update copier set second = first; Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
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
> 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
>
> So what I want to know is, is it possible? If it is and my query is
> incorrect and anyone can tell me what's wrong with it that would be great.
INSERT adds new rows to a table. It will not copy a value from one column to another. The UPDATE
statement can do this however.
UPDATE table set second = first;
Regards,
Richard Broersma Jr.
Thanks a lot, guys. I wanted to know if it was possible. Thanks for setting
me on the right path.
-Wilf.
> > 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
> >
> > So what I want to know is, is it possible? If it is and my query is
> > incorrect and anyone can tell me what's wrong with it that would be
>great.
_________________________________________________________________
Get FREE company branded e-mail accounts and business Web site from
Microsoft Office Live
http://clk.atdmt.com/MRT/go/mcrssaub0050001411mrt/direct/01/