Solved

Delete from a table using a multi column primary key

Posted on 2011-02-11
2
411 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 53

Accepted Solution

by:
Huseyin KAHRAMAN 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

739 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