using IN and EXIST in a complex Oracle SQL Statment
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.)
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
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
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
ASKER
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
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
select * from component
where (analysis,version) in (select name,version from analysis where removed='T')
where (analysis,version) in (select name,version from analysis where removed='T')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
perfect...just what I need
I'm not understanding where removed='F' comes into play since you have "and REMOVED = 'T'"