Solved

using IN and EXIST in a complex Oracle SQL Statment

Posted on 2011-09-27
7
370 Views
Last Modified: 2012-05-12
I need to remove records from a child table where the parent table has field called removed set to true.  Normally this be a straight forward change however there is versioning so for I need to leave both the child versions and the parent version of the where removed is set to F (false) in tact.

here is what I have so far but it still pulling all versions (starting with a select statement to see what I pull vs running the delete statment and getting into trouble...if someone could help me with the select statement I greatly apprciate.)


SELECT ANALYSIS, NAME, VERSION  from COMPONENT where EXISTS (SELECT Name from Analysis WHERE  COMPONENT.ANALYSIS = ANALYSIS.NAME  and REMOVED = 'T') ORDER BY ANALYSIS

Open in new window

0
Comment
Question by:gilnari
  • 4
  • 2
7 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36709363
Just to make sure I understand, can you provide some sample data and expected results?

I'm not understanding where removed='F' comes into play since you have "and REMOVED = 'T'"
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36709425
Given component with analysis X

if there is an analysis.removed T and an analysis.removed F  then your exists will return true  for either the false or the true condition.

Try this query to check for that condition


SELECT   c.analysis, c.name, c.version, MIN(a.removed), MAX(a.removed)
    FROM component c, analysis a
   WHERE c.analysis = a.name
ORDER BY analysis

if min and max are the same, then IN/EXISTS will work.  If min and max are different then it won't work because both T and F will find matches
0
 

Author Comment

by:gilnari
ID: 36709676
There are two tables Analysis and Component.  In the Analysis Table we have Name, Version, Removed.  In the Component table (child) we have Analysis, ComponentName, Version).  
Analysis.Name = Component.Analysis
Analysis.Version = Compoenent.Versoin

I need to  remove all Components from the Components table where Version is the Same abetween the tables nd where Removed = 'T' in the Analysis Table.

Example Data.Analysis Table (Analysis Name, Version, Removed)
XYZ , 1, T
XYZ, 2, T
YXZ, 3, F

Component  (analysis, componentname, version)
XYZ, Start, 1
XYZ, Start, 2
XYZ, Start, 3

I want the select statement to return for the Comnpent Table
XYZ, Start, 1
XYZ, STart, 2

Eventually this will become a delete statmenet where only thing left in the component table is XYZ, Start, 3 and in the analysis table XYZ, 3, F
0
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.

 
LVL 73

Expert Comment

by:sdstuber
ID: 36709719
select * from component
where (analysis,version) in (select name,version from analysis where removed='T')
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 36709728
or

select * from component c
where exists
(select null from analysis a
where a.removed='T'
and a.name = c.analysis
and a.version=c.version)
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36709740
or

select c.* from component c, analysis a
where a.removed='T'
and a.name = c.analysis
and a.version=c.version


note this one will work for the data you have above, but is not functionally equivalent  in general to the other two
0
 

Author Closing Comment

by:gilnari
ID: 36709975
perfect...just what I need
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help with Oracle syntax 4 56
oracle query help 29 77
Oracle 12c patching 1 61
Bash Script to Analyze Oracle Schemas 11 84
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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 …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

920 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

14 Experts available now in Live!

Get 1:1 Help Now