CODEWITHSUNDEEP
sqloracle-databasesql-insert
lovemyjob
lovemyjob

Reputation: 579

Copy rows from and within same oracle SQL table and changing some column values

I have a table called table1 within oracle DB that looks like this: 

ID  USERID  FRUIT   COLOR
1   10      APPLE   BLUE
2   10      ORANGE  RED
3   20      BANANA  YELLOW

I would like to build a query that would: - select all rows from userID 10 and copy them over to the same table, keeping all fields untouched apart from ID (I would guess it should automatically increment itself?). Edit: The increment part will increment itself as long as it is DB column (not user created).

So the result I would like to have is for userID 20 to have userID 10 rows as per below:

ID  USERID  FRUIT   COLOR
1   10      APPLE   BLUE
2   10      ORANGE  RED
3   20      BANANA  YELLOW
4   20      APPLE   BLUE
5   20      ORANGE  RED

Below is my trial query - will it work?

INSERT INTO table1
SELECT * FROM table1
WHERE USERID=10;

Upvotes: 1

Views: 2308

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

List the columns and values that you want:

INSERT INTO table1(USERID, FRUIT, COLOR)
    SELECT 20, FRUIT, COLOR
    FROM table1
    WHERE USERID = 10;

If the id is not generated automatically on insert, you can calculate the value:

INSERT INTO table1(ID, USERID, FRUIT, COLOR)
    SELECT tt1.maxid + ROW_NUMBER() OVER (ORDER BY NULL) as ID,
           20, FRUIT, COLOR
    FROM table1 t1 CROSS JOIN
         (SELECT MAX(ID) as maxid FROM table1) tt1
    WHERE USERID = 10;

Upvotes: 3

MT0
MT0

Reputation: 167774

Use a sequence for the ID column:

CREATE SEQUENCE table1__id__seq;

and use that for all your inserts.

Then you can use:

INSERT INTO table1( id, userid, fruit, color )
  SELECT table1__id__seq.NEXTVAL,
         20,
         fruit,
         color
  FROM   table1
  WHERE  userid = 10;

Upvotes: 0

Related Questions