So, the continaution of another question...
I have merge which task is to create the junction table rows from an existing table which only can represent 1-1 connection between entities (PROJECT) and from a table which can represent N-1 connecton (worker) the junction table is (PROJECT-WORKER), everyproject should have a dedicated leaders, and every worker should have a project but now i should have an n-n connection... This merge would do the job:
But unfortunately, multiple matched then insert
branch is not a "feature" in sql, how can i workout this problem. The original query---)
MERGE INTO WORKERPROJECT TARGET
USING (SELECT distinct
w.worker_id,
w.worker_type,
w.project_id worker_project_id,
p.project_id project_project_id,
p.dedicated_project_leader,
p.dedicated_lead_developer,
p.dedicated_lead_consultant,
p.dedicated_supervisor
from WORKER w
join PROJECT p on w.project_id = p.project_id
) SOURCE
ON (SOURCE.worker_type is null)
WHEN NOT MATCHED THEN INSERT (TARGET.FK_WORKER_ID, TARGET.FK_PROJECT_ID,TARGET.IS_ACTIVE,POSITION)
VALUES (SOURCE.WORKER_ID,SOURCE.worker_project_id,'ACTIVE',SOURCE.worker_type);
WHEN NOT MATCHED THEN INSERT (FK_WORKER_ID, FK_PROJECT_ID,IS_ACTIVE,POSITION)
VALUES (SOURCE.dedicated_project_leader,SOURCE.project_project_id,'ACTIVE','PROJECTVEZETŐ');
WHEN NOT MATCHED THEN INSERT (FK_WORKER_ID, FK_PROJECT_ID,IS_ACTIVE,POSITION)
INSERT (FK_WORKER_ID, FK_PROJECT_ID,IS_ACTIVE,POSITION) VALUES (SOURCE.dedicated_lead_developer,SOURCE.project_project_id,'ACTIVE','FEJLESZTŐVEZETŐ');
WHEN NOT MATCHED THEN INSERT (FK_WORKER_ID, FK_PROJECT_ID,IS_ACTIVE,POSITION)
INSERT (FK_WORKER_ID, FK_PROJECT_ID,IS_ACTIVE,POSITION) VALUES (SOURCE.dedicated_lead_consultant,SOURCE.project_project_id,'ACTIVE','KONZULENSVEZETŐ');
WHEN NOT MATCHED THEN INSERT (FK_WORKER_ID, FK_PROJECT_ID,IS_ACTIVE,POSITION)
INSERT (FK_WORKER_ID, FK_PROJECT_ID,IS_ACTIVE,POSITION) VALUES (SOURCE.dedicated_supervisor,SOURCE.project_project_id,'ACTIVE','SUPERVISOR');
Actually you can. Thanks to the insert all
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9014.htm#i2095116