• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

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.MYTABLE_2_Key AND
T1.MYTABLE_3_Key=T2.MYTABLE_3_Key)
WHEN MATCHED THEN UPDATE SET INSERT_DTM=SYSDATE
Delete WHERE (1=1);
0
gswitz
Asked:
gswitz
1 Solution
 
HainKurtSr. System AnalystCommented:
you can delete first then run update instead of merge

delete from mytable
where (col1,col2) in (select col1,col2 from mytable2);

then write update or merge query...
0
 
gswitzAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now