Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Delete from a table using a multi column primary key

Posted on 2011-02-11
2
Medium Priority
?
417 Views
Last Modified: 2012-08-14
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
Comment
Question by:gswitz
2 Comments
 
LVL 61

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 34875102
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
 

Author Closing Comment

by:gswitz
ID: 34883244
Thanks!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question