CODEWITHSUNDEEP
sqloracle-database
Hyllus
Hyllus

Reputation: 11

Oracle SQL: Copy and update records within a table

I have a table with 30 records for 'Orders'. I'm trying to

  1. copy these records,
  2. change several fields to make these records 'Sales' records, and
  3. append the updated records to the table.

There is an ID field that's sequential. I'm trying to use the following code which is not working:

INSERT INTO TABLE_01 (
    ID, 
    CONTROL_CODE, 
    CODE, 
    RULE_CODE, 
    CAT, 
    INPUT_VALUES,
    UPDATER,
    UPDATE_TIMESTAMP) 
    SELECT 
        (SELECT MAX(ID) FROM TABLE_01) + 1,
        CONTROL_CODE, 
        CODE, 
        'SALES',
        CAT, 
        INPUT_VALUES,
        'ME',
        SYSDATE
    FROM
        TABLE_01
    WHERE
        ID IN (
            SELECT
                ID
            FROM
                TABLE_01
            WHERE
                RULE_CODE = 'ORDERS');

Any suggestions would be greatly appreciated

Upvotes: 1

Views: 45

Answers (1)

kfinity
kfinity

Reputation: 9091

The problem is that this code

(SELECT MAX(ID) FROM TABLE_01) + 1,

Returns the same value for every row. It doesn't do one insert, run the query again, do the second insert, etc. Instead it runs the query once, and then batch inserts all of the rows that come back. If you run the select statement by itself, without the insert, you'll see what I mean. Try this instead:

(SELECT MAX(ID) FROM TABLE_01) + rownum,

Upvotes: 1

Related Questions