Hello all,
i have some problem to format a merge statement as I want to . I tried to wrap my head around vadims blog posts about this topic but I fear they are above my pay grade. consider the following example.
MERGE INTO crew tgt USING ( SELECT
crew_seqno
,inoutdte
,cardtype
,tfirstnm
FROM
crew@xxx
WHERE
TO_CHAR(
create_date
,'YYYYMMDD'
) = p_create_date_yyyymmdd
)
src ON ( src.crew_seqno = tgt.crew_seqno )
WHEN NOT MATCHED THEN INSERT (
crew_seqno -- this should be lined up
,inoutdte -- this should be lined up
,cardtype -- this should be lined up
,tfirstnm -- this should be lined up
) VALUES (
src.crew_seqno -- this should be lined up
,src.inoutdte -- this should be lined up
,src.cardtype -- this should be lined up
,src.tfirstnm -- this should be lined up
)
WHEN MATCHED THEN UPDATE SET tgt.inoutdte = src.inoutdte
,tgt.cardtype = src.cardtype
,tgt.tfirstnm = src.tfirstnm;
It should look like this
MERGE INTO crew tgt USING ( SELECT
crew_seqno
,inoutdte
,cardtype
,tfirstnm
FROM
crew@xxx
WHERE
TO_CHAR(
create_date
,'YYYYMMDD'
) = p_create_date_yyyymmdd
)
src ON ( src.crew_seqno = tgt.crew_seqno )
WHEN NOT MATCHED THEN INSERT (
crew_seqno
,inoutdte
,cardtype
,tfirstnm
) VALUES (
src.crew_seqno
,src.inoutdte
,src.cardtype
,src.tfirstnm
)
WHEN MATCHED THEN UPDATE SET tgt.inoutdte = src.inoutdte
,tgt.cardtype = src.cardtype
,tgt.tfirstnm = src.tfirstnm;
How can I achieve this result ?
I use version 17.4
Thanks a lot for your help.
Norbert