Copy record and insert into the same table

Hi,
My requirement is something like this. I will try to explain as much as possible and please let me know if anything is not clear.

Please find the sample data for hdr, dtl and table below.

Join tableA with header to find the type.
SELECT A.TYPE
FROM TABLEA A, HDR_TBL HDR
WHERE A.ID = HDR.ID;

1)
If id is of type A then do nothing;

2)
if id is of type B then copy the whole record in hdr_tbl and insert back into the same table hdr_tbl and update col2 to 123 and update transaction column with max(transaction)+1 in the hdr table. At the same time get the corresponding detail record by joining hdr_tbl and dtl_tbl and update col2 in detail and insert back.

3)
if id is of type c then we have to insert two records one is same a B and another record with col2 to 522 and do the same in detail table.

SELECT * FROM HDR_TBL; ---(HEADER)
ID	COL1	COL2	transaction
123	ABC	XYZ	1
456	MNO	WXY	1

SELECT * FROM DTL_TBL; ---(DETAIL)
ID	COL1	COL2	Transaction
123	ABC	XYZ	1
123	ABC	XYZ	2
123	ABC	XYZ	3
456	MNO	WXY	1


Result:
SELECT * FROM HDR_TBL; ---(HEADER)
ID	COL1	COL2	transaction
123	ABC	XYZ	1
456	MNO	WXY	1
456	MNO	123	56 ---max transaction+1
456	MNO	522	57

SELECT * FROM DTL_TBL; ---(DETAIL)
ID	COL1	COL2	Transaction
123	ABC	XYZ	1
123	ABC	XYZ	2
123	ABC	XYZ	3
456	MNO	WXY	1
456	MNO	WXY	2
456	MNO	WXY	56
456	MNO	WXY	57

Select allOpen in new window