UPDATE before INSERT (Oracle)

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
   ---- ---------- ---------- 
    开发者_StackOverflow01   data2       test2
    02   data2       test2
    03   data1       test1

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

Thanks, Jonas

---

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.

---

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).

---

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.

---

insert into s2.table1 (
  select id+1, data1, data2 
  from s1.table1 
  where id=(select max(id) from s1.table1)
);