Home » SQL & PL/SQL » SQL & PL/SQL » how to clone the record
how to clone the record [message #283239] Mon, 26 November 2007 07:44 Go to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
hi here iam having one record say but in my original table there are upto 70 columns for example


tid tname tcat startdate enddate

01 x a 05/05/2005 12/11/2007

02 yz b 02/02/2005

04 nb c 10/10/2003 03/03/2006





I want to update the second row at the same time i want old second row which has not updated ,i want to keep track of my transactions.

i want to update and keep old row before transaction like this

second row should be updated with some date and tcat should be as it is

and again this second row should added as a new record with startdate as enddate of the original second row with tcat changed

my main confussion is how this tid should be given automatically
how to insert.


tid tname tcat startdate enddate

01 x a 05/05/2005 12/11/2007

02 yz b 02/02/2005 11/11/2006

04 nb c 10/10/2003 03/03/2006

05 yz c 11/11/2006




[Updated on: Mon, 26 November 2007 08:11]

Report message to a moderator

Re: how to clone the record [message #283243 is a reply to message #283239] Mon, 26 November 2007 08:00 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
I'm not quite sure that I understand you, but maybe you can use something like :
Quote:


insert into your_table select <column_1>, column_2, .... SYSDATE,.... from your_table where your_condition;


(it's just an example)

In this way the records (filtered by the where clause) will be inserted as a new record.

Will you next time add your oracle version etc. in the message

Best Regards,

Martijn
Re: how to clone the record [message #283245 is a reply to message #283239] Mon, 26 November 2007 08:02 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Some thing like ,

Insert into table_name (tid, tname, tcat, startdate, enddate)
select New_tid, --(New Id as per appln logic)
       tname, tcat, startdate, enddate
from table_name WHERE tid ='xxx'

Update table_name 
SET tid= New_tid
WHERE tid ='xxx'


Hint :
select  LPAD(TO_NUMBER(LTRIM('04','0'))+1,2,'0') from dual



Thumbs Up
Rajuvan

[Updated on: Mon, 26 November 2007 08:03]

Report message to a moderator

Previous Topic: Query doubt
Next Topic: sqlca.sqlcode when oracle goes off
Goto Forum:
  


Current Time: Sat Dec 10 16:22:07 CST 2016

Total time taken to generate the page: 0.07710 seconds