Re: Inserting Multiple Rows repeats the ID

juneauej_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.

select
  ( select max(x) from t ) + rownum,
  ...
  ...

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------