UPDATE before INSERT (Oracle)

Question

hello,

I have two schemas S1 and S2 with identical table structure. I`ll start with example:

on S1.table1 I have:

ID   DATA1       DATA2

---

01   data1       test1
02   data1       test1

on S2.table1 I have:

 ID   DATA1       DATA2

---

01   data2       test2
02   data2       test2

Is it possible to select one row (from S1.table1) update it (change value of ID column) and insert it into S2.table1? I want to write single SQL query which will be used as prepared statement (Java).

Final result on S2 should be:

 ID   DATA1       DATA2

---

01   data2       test2
02   data2       test2
03   data1       test1

so i inserted first row from S1.table1, and changed only ID.

Thanks, Jonas

Answer 1

How about

INSERT INTO S2
SELECT 03, DATA1, DATA2 FROM S1 WHERE ID=01

That is, you just select the data you want, but explicitly indicate any replacement data.

Answer 2

INSERT INTO S2 (Data1, Data2) SELECT Data1, Data2 FROM S1 WHERE ID=xxx

Assuming the ID in S2 is autoincremented (via sequence and trigger for instance).

Answer 3

You must use a sequence:

insert into s2.table1
select seq.next_val, data1, data2
from s1.table1

The sequence will always count up. So when you run this again, the new rows will be "appended" to the existing ones.