Solved

Delete from a table using a multi column primary key

Posted on 2011-02-11
2
408 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 51

Accepted Solution

by:
HainKurt earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

911 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now