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

Open in new window

cutie_smilyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KarlisBCommented:
am quite confused, what aer those "type" a,b,c ? some kind identificator?

another question,
"f 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"

i don't get this at all.
if i get you right, you want to insert selected result row into hdr_tbl with updated col2 and transaction columns?

"At the same time get the corresponding detail record by joining hdr_tbl and dtl_tbl and update col2 in detail and insert back.."
if am right, you want same time update column2 in detail table?

"
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."
I am stunned, don't get anything at all  :D

"Copy record and insert into the same table" <--- none does copy records in databases, that is jut confusing as the rest is

sorry, my english knowledge base is low.

Ok, lets see.





 CREATE OR REPLACE FUNCTION some_func(a_type VARCHAR2(10 BYTE),id_i integer,col1_i VARCHAR2(10 BYTE),transaction_1 integer)
  RETURN NUMBER IS
  BEGIN
  
  case a_type
    when a then 
   -- null;
    when b then dbms_output.put_line('You selected two');
            insert into HDR_TBL(ID,COL1,COL2,transaction) Values( id_i,col1_i,123,transaction); 

            insert into DTL_TBL(ID,COL1,COL2,transaction) Values( id_i,col1_i,123,transaction);
    when c then dbms_output.put_line('You selected three');
  --  .....
  end case;

        RETURN 1;
  END some_func;


-- to update/insert values do:


SELECT A.TYPE,ID,COL1,COL2,Transaction ,some_func(a.type,id,col1,max(transaction)+1) as something
FROM TABLEA A, HDR_TBL HDR
WHERE A.ID = HDR.ID;


--hope this helps, thus i dont get anything at all :P
--chears

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KarlisBCommented:
ahh,

never mind   "dbms_output.put_line('You selected two');" line in the code, forgot to cut them out of the code.
0
tomcatkevCommented:
Roughly speaking, you can code CASE statement to accomplish all the variations in logic you are looking at, on a column by column basis.  I just show the example for col2 which seems clear enough.
insert into hdr_tbl
SELECT 
(case 
  when A.TYPE='A' then null
  when A.TYPE='B' then 123
  when A.TYPE='C' then 522
 end) col2
FROM TABLEA A, HDR_TBL HDR
WHERE A.ID = HDR.ID
and A.TYPE<>'A'

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.