Re: Inserting Multiple Rows repeats the ID

From: gazzag <gareth_at_jamms.org>
Date: Thu, 14 Jun 2007 07:11:13 -0700
Message-ID: <1181830273.038435.131360@i13g2000prf.googlegroups.com>

On 14 Jun, 15:01, junea..._at_gmail.com wrote:
> Hi, in the statement below I am trying to insert multiple rows into
> the HAND table. CARDID is the primary key of HAND
>
> INSERT INTO HAND
> (CARDID,
> SUITID,
> RANKID)
> SELECT (SELECT MAX(H.CARDID) FROM HAND H) + 1,
> DECK.SUITID,
> DECK.CARDID,
> FROM DECK
> WHERE ....;
>
> The problem is when I run this statement, it repeats the CARDID. It
> never increments. For example if the max CARDID in HAND was 217. It
> will insert 10 rows (or however many the WHERE statement allows) all
> with the CARDID 218. I don't have any nextval, max_sequence_num, or
> IDENTITY columns in this table. Is there a way to increase the CARDID
> with each entry that is added in and keep it simple?
> If there is no solution, would there be a way if I created a temporary
> table?
> This is on Oracle 9i.

You should be using an Oracle SEQUENCE. Review the documentation here: http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#sthref883 and here: http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6015.htm#i2067093 for the concept of SEQUENCES, the syntax and an example.

If, for example, you created a sequence called HAND_SEQ as follows:

CREATE SEQUENCE hand_seq
 START WITH 100
 INCREMENT BY 1
 NOCACHE
 NOCYCLE; Then your INSERT statement should end up looking something like the following:

INSERT INTO HAND

         (CARDID,
         SUITID,
         RANKID)
 SELECT (HAND_SEQ.NEXTVAL,     <------- this causes the value of
CARDID to be the next value in the sequence
         DECK.SUITID,
         DECK.CARDID,

 FROM DECK
 WHERE ....; HTH -g Received on Thu Jun 14 2007 - 09:11:13 CDT