gswitz
asked on
Delete from a table using a multi column primary key
I can efficiently select the multi column key to delete from my target table and I'm curious if a merge is the most efficient way to execute this delete...
PK on MYTABLE_2 is MYTABLE_2_Key.
PK on MYTABLE_3 is MYTABLE_3_Key.
PK on MYTABLE_2_3_XREF is MYTABLE_2_Key, MYTABLE_3_Key
MERGE INTO MYTABLE_2_3_XREF T1 USING(
SELECT T1.MYTABLE_2_Key, T2.MYTABLE_3_Key
FROM (SELECT Key_Col_1
FROM MYTABLE_2
WHERE MYTABLE_2_Key in (1,2,3)) T1,
(SELECT KEY_COL_2
FROM MYTABLE_3
WHERE MYTABLE_3_Key IN (4,5,6)) T2
) T2
ON (T1.MYTABLE_2_Key=T2.MYTAB LE_2_Key AND
T1.MYTABLE_3_Key=T2.MYTABL E_3_Key)
WHEN MATCHED THEN UPDATE SET INSERT_DTM=SYSDATE
Delete WHERE (1=1);
PK on MYTABLE_2 is MYTABLE_2_Key.
PK on MYTABLE_3 is MYTABLE_3_Key.
PK on MYTABLE_2_3_XREF is MYTABLE_2_Key, MYTABLE_3_Key
MERGE INTO MYTABLE_2_3_XREF T1 USING(
SELECT T1.MYTABLE_2_Key, T2.MYTABLE_3_Key
FROM (SELECT Key_Col_1
FROM MYTABLE_2
WHERE MYTABLE_2_Key in (1,2,3)) T1,
(SELECT KEY_COL_2
FROM MYTABLE_3
WHERE MYTABLE_3_Key IN (4,5,6)) T2
) T2
ON (T1.MYTABLE_2_Key=T2.MYTAB
T1.MYTABLE_3_Key=T2.MYTABL
WHEN MATCHED THEN UPDATE SET INSERT_DTM=SYSDATE
Delete WHERE (1=1);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER