Thread: Insert Question
hhi all, long time no askie question, but here goes... i want to insert sequential document numbers into a products table my current setup is like so: product_number document_number 1001 1002 1006 1005 i want an insert statement that will yield the following: product_number document_number 1001 42000001 1002 42000002 1006 42000003 1005 42000004 relative order means nothing, but i do want sequential document numbers. how can i go about getting this done as efficiently as possible? as always, tia. __________________________________________________________________________________________ Check out the New Yahoo! Mail - Fire up a more powerful email and get things done faster. (http://advision.webevents.yahoo.com/mailbeta)
am Thu, dem 02.11.2006, um 12:01:51 -0800 mailte operationsengineer1@yahoo.com folgendes:
> hhi all,
>
> long time no askie question, but here goes...
>
> i want to insert sequential document numbers into a
> products table
>
> my current setup is like so:
>
> product_number document_number
> 1001
> 1002
> 1006
> 1005
>
> i want an insert statement that will yield the
> following:
>
> product_number document_number
> 1001 42000001
> 1002 42000002
> 1006 42000003
> 1005 42000004
Create a sequence that starts with 42000001, and insert
nextval('this_sequence') for this column. (document_number).
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
I would look at creating a temp table as: select product_number,generate_series(4200001,4200001+(select count(*) from product_number)) order by product_number; Then rename the temp table back to the original table. Sean -----Original Message----- From: operationsengineer1@yahoo.com [mailto:operationsengineer1@yahoo.com] Sent: Thu 11/2/2006 3:01 PM To: pgsql-novice@postgresql.org Subject: [NOVICE] Insert Question hhi all, long time no askie question, but here goes... i want to insert sequential document numbers into a products table my current setup is like so: product_number document_number 1001 1002 1006 1005 i want an insert statement that will yield the following: product_number document_number 1001 42000001 1002 42000002 1006 42000003 1005 42000004 relative order means nothing, but i do want sequential document numbers. how can i go about getting this done as efficiently as possible? as always, tia. __________________________________________________________________________________________ Check out the New Yahoo! Mail - Fire up a more powerful email and get things done faster. (http://advision.webevents.yahoo.com/mailbeta) ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
> hhi all,
>
> long time no askie question, but here goes...
>
> i want to insert sequential document numbers into a
> products table
>
> my current setup is like so:
>
> product_number document_number
> 1001
> 1002
> 1006
> 1005
>
> i want an insert statement that will yield the
> following:
>
> product_number document_number
> 1001 42000001
> 1002 42000002
> 1006 42000003
> 1005 42000004
>
> relative order means nothing, but i do want sequential
> document numbers.
>
> how can i go about getting this done as efficiently as
> possible?
>
> as always, tia.
create sequence tmp_seq start with 42000000;
update products set document_number = nextval('tmp_seq');
Would this do what you want?
Regards,
Richard Broersma Jr.
> > hhi all,
> >
> > long time no askie question, but here goes...
> >
> > i want to insert sequential document numbers into
> a
> > products table
> >
> > my current setup is like so:
> >
> > product_number document_number
> > 1001
> > 1002
> > 1006
> > 1005
> >
> > i want an insert statement that will yield the
> > following:
> >
> > product_number document_number
> > 1001 42000001
> > 1002 42000002
> > 1006 42000003
> > 1005 42000004
> >
> > relative order means nothing, but i do want
> sequential
> > document numbers.
> >
> > how can i go about getting this done as
> efficiently as
> > possible?
> >
> > as always, tia.
>
> create sequence tmp_seq start with 42000000;
>
> update products set document_number =
> nextval('tmp_seq');
>
> Would this do what you want?
it led me in the right direction.
here is the code:
create sequence tmp_seq start with 42000005;
update products set document_number =
nextval('tmp_seq')
where ocument_number is null;
i had to do it this way because i actually had a few
entries in the db already. it did work just fine,
though.
i learned to use...
drop sequence tmp_seq;
in order to reuse the sequence as i was playing around
with functionality. otherwise, it would return an
error b/c the sequence already existed.
thanks again.
____________________________________________________________________________________
Access over 1 million songs - Yahoo! Music Unlimited
(http://music.yahoo.com/unlimited)
> create sequence tmp_seq start with 42000005;
>
> update products set document_number =
> nextval('tmp_seq')
> where ocument_number is null;
>
> i had to do it this way because i actually had a few
> entries in the db already. it did work just fine,
> though.
In this case you could:
create sequence temp_seq start with (select max(document_number) from products);
>
> i learned to use...
>
> drop sequence tmp_seq;
>
> in order to reuse the sequence as i was playing around
> with functionality. otherwise, it would return an
> error b/c the sequence already existed.
if you want to use the same sequence over again you could:
select set_val('temp_seq', select max(document_number) from products));
and then do your update.
you will not need to drop your sequence this way.
Regards,
Richard Broersma Jr.
> > create sequence tmp_seq start with 42000005;
> >
> > update products set document_number =
> > nextval('tmp_seq')
> > where ocument_number is null;
> >
> > i had to do it this way because i actually had a
> few
> > entries in the db already. it did work just fine,
> > though.
>
> In this case you could:
>
> create sequence temp_seq start with (select
> max(document_number) from products);
>
>
> >
> > i learned to use...
> >
> > drop sequence tmp_seq;
> >
> > in order to reuse the sequence as i was playing
> around
> > with functionality. otherwise, it would return an
> > error b/c the sequence already existed.
>
> if you want to use the same sequence over again you
> could:
>
> select set_val('temp_seq', select
> max(document_number) from products));
> and then do your update.
>
> you will not need to drop your sequence this way.
Richard, good stuff. thanks!
__________________________________________________________________________________________
Check out the New Yahoo! Mail - Fire up a more powerful email and get things done faster.
(http://advision.webevents.yahoo.com/mailbeta)