Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2006
    Posts
    56

    Unanswered: Increment primary key in Insert statment

    I need to create new rows based on rows that already exist, so that there are two entries for each structure_code. I checked the DDL, and the primary key (rate_id) is not set to auto increment. So how can I create unique indexes for the new rows? I am using 10g

    This is what I tried so far. I get a unique constraint violation on rate_id

    insert into billing_rate

    select
    (select max(rate_id) from billing_rate),
    STRUCTURE_CODE,
    RATE_TYPE_CODE,
    BILLRATE_START,
    BILLRATE_FINISH,
    BILLING_RATE,
    CURRENCY_CODE,
    BILLING_TIME_UNIT,
    USER_NAME,
    DATE_CREATED
    from billing_rate where rate_type_code = 'RsCostRate'

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You will sort of need SEQUENCES for what you're trying to achieve.

  3. #3
    Join Date
    Aug 2006
    Posts
    56
    I can't change the schema. I am stuck with it the way it is.

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You said you want to "create unique indexes", which I assume will be unique keys (being natural or not), so.. I suggest you use a SEQUENCE and then just: (a) use a procedure selecting the sequence when inserting into billing_rate or (b) feed the rate_id from the sequence in a trigger.

    The reason you're getting a unique constrain violation is *probably* because rate_id is unique, and you're select'ing the MAX value from it to insert it into the table again. You will have to add some *salt* to it to make it unique, reason I am suggesting you to use a sequence.

    I am not suggesting you to change your schema..

  5. #5
    Join Date
    Aug 2006
    Posts
    56
    Sorry, I should be more speciific. I want to create unique index values for each row.

    I looked at the sequence command. Can it alter the schema just for the course of the transaction?

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    A sequence is not in any way shape or form a part of a table. Create the sequence, use it, remove it. For example,

    select max(rate_id) from billing_rate where rate_type_code = 'RsCostRate';

    create sequence junk start with max_rate_plus_one;

    insert into billing_rate
    select
    junk.nextval,
    STRUCTURE_CODE,
    RATE_TYPE_CODE,
    BILLRATE_START,
    BILLRATE_FINISH,
    BILLING_RATE,
    CURRENCY_CODE,
    BILLING_TIME_UNIT,
    USER_NAME,
    DATE_CREATED
    from billing_rate where rate_type_code = 'RsCostRate';

    drop sequence junk;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Aug 2006
    Posts
    56
    Thank you, beilstwh. I just have to find out if I can get the db rights to do the sequence.

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    If nobody else inserts into the table, you can use ROWNUM pseudocolumn
    Code:
    insert into billing_rate (<column_list>)
    select
    (select max(rate_id) from billing_rate) + rownum,
    <other_columns>
    from billing_rate
    where rate_type_code = 'RsCostRate'
    Just curious how you prevent users from assignining the same RATE_ID when inserting.

  9. #9
    Join Date
    Aug 2006
    Posts
    56
    flyboy, the application usually does the insert. So the transactions are controlled by JDBC. I will give the rownum a try. But now they tell me they don't need the insert.
    -----------


    Also, thank you JMartinez for your assistance

  10. #10
    Join Date
    Aug 2007
    Location
    France
    Posts
    14

    Cool

    The easiest way is to combine sequence with trigger :
    - create a sequence on rate_id
    - create trigger "ON INSERT" that, at each insert in your billing_rate table, will insert "your_sequence.nextval" in the rate_id column

    Thanks to that you can do all your inserts without specifying any value for the rate_id (and not at each time find the max value : it can be long for big tables). The rate_id field will be automatically incremented by the trigger/sequence at each insert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •