Hi All,
I am using oracle :- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
I have two table called
TableA which has 4 columns i.e. entity_id, entity_type, entity_name,last_update_date
Table B which has 6 columns i.e. entity_id,entity_type,entity_name,last_update_date,project_id,project_name.
Table A contains 100 records.
Table B contains 300 records.
Both the table has different records.
We use Table B for archiving historical data from Table A.
In Table B we will archive data from Table A and need to populate Project_id and Project_name based on the comparasion of entity_id(Table A) and populate in table B.
For the above purpose i use merge statment.
Merge query
MERGE INTO Table B e
USING (SELECT * FROM Table A WHERE last_update_date > trunc(sysdate - 180) ) h
ON (e.entity_id = h.entity_id)
WHEN MATCHED THEN
UPDATE SET project_id = nvl((
CASE h.ENTITY_TYPE
WHEN 6
THEN
(SELECT p.project_id
FROM ppm.pm_projects p
WHERE p.project_id = h.entity_id
)
else 0
end),0)
WHEN NOT MATCHED THEN
INSERT ( entity_id,entity_type,entity_name,last_update_date,project_id,project_name)
VALUES ( h.entity_id,h.entity_type,h.entity_name,h.last_update_date,
CASE h.ENTITY_TYPE
WHEN 6
THEN
(SELECT p.project_id
FROM ppm.pm_projects p
WHERE p.project_id = h.entity_id
)
else 0
end
);
So basically i am trying to insert some additional values based on the another column value while doing the merge insert clause.
I am trying to achieve the above functionality, but it is not working as expected. I think while insert we cannot reference to the same column for query(not sure about this, read from some forumn).
Can any one please assist me on this. Whether there is a possibility to achieve through merge at all.
Thanks & Regards,
Sameet Kumar Patro
Email:- sameet.patro@gmail.com