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

Question

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;

Which version of Oracle are you using?

Kaushik Nayak
– Kaushik Nayak

2018-02-14 12:23:44 +00:00
Commented Feb 14, 2018 at 12:23 — Kaushik Nayak
– Kaushik Nayak, Commented Feb 14, 2018 at 12:23
Oracle version 10.2

lovemyjob
– lovemyjob

2018-02-14 13:39:52 +00:00
Commented Feb 14, 2018 at 13:39 — lovemyjob
– lovemyjob, Commented Feb 14, 2018 at 13:39

Gordon Linoff · Accepted Answer · 2018-02-14 13:21:06Z

3

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;

edited Feb 14, 2018 at 13:21

answered Feb 14, 2018 at 11:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

lovemyjob Over a year ago

Can I do INSERT INTO table1(*)?

lovemyjob Over a year ago

And will the ID auto increase?

MatBailie Over a year ago

@lovemyjob - If you're going to populate all the columns, and won't be making use of defaults or IDENTITY, etc, just leave the (a, b,c) out... INSERT INTO table1 SELECT ........ This has a significant weakness though; if the table structure changes (columns moved, or added) it won't necessarily do what you want. Specifying the columns is strongly recommended.

MatBailie Over a year ago

@lovemyjob - That depends on the table definition. Is the id column defined as IDENTITY? Is there a trigger to automatically populate it from a sequence? Or are you responsible for managing the id yourself? (We can't answer that for you, you have to investigate your setup...)

lovemyjob Over a year ago

I investigated the setup, ID (increasing number) is being created automatically, when the end user creates a new row in database. So question is - will the ID increase when I use INSERT query? Or shall I play safe and increment it myself to prevent having same ID values in one table?

|

MT0 · Accepted Answer · 2018-02-14 11:59:50Z

0

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;

answered Feb 14, 2018 at 11:59

MT0

173k12 gold badges70 silver badges136 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related