Re: Simple Delete Gets Complex..any Ideas
One thing that's been overlooked in these discussions is the fact that
you need TWO sets of parens in order to get this statement to work
properly. I've found that on updates and deletes, one needs one
set for the subquery and one set for the IN clause. To use the
delete statement below as an example:
delete from Table_A
where Table_A.PK in ((Select Table_B.FK from Table_B where {your query
condition}))
The results differ if you don't have both sets of parens. I hope
this helps!
Tracy
}
} John,
} You didn't say what the target database is. However you can try this
} on whatever the database you have. It may or may not work depending
} on the target.
}
} delete from Table_A
} where Table_A.PK in (Select Table_B.FK from Table_B where {your query
} condition})
}
} John C. Pugh wrote:
} >
} > Objective:
} >
} > Deleting rows from table "Table_A" based on results of a subquery which
} > requires an additional table "Table_B".
} >
} > current syntax:
} >
} > Delete from Table_A where
} >
} > (select * from Table_A, Table_B where
} > Table_A.PK = Table_B.FK (+))
} >
} > needless to say this doesn't work, I also attempted the following
} >
} > Delete from Table_A where
} > EXISTS
} > (select * from Table_A, Table_B where
} > Table_A.PK = Table_B.FK (+))
} >
} > If my subquery returned one row, Table_A was history (LoL..thank God
} > for
} > RollBacks)!!
} >
} > I also tried:
} >
} > Delete from
} > (select * from Table_A, Table_B where
} > Table_A.PK = Table_B.FK (+))
} >
} > But this attempted fails to "SPECIFY" exactly which table to apply the
} > delete method to.
} >
} > I know in Sybase you would do something like
} >
} > delete Table_A from Table_A, Table_B where Table_A*=Table_B
} >
} > Basically, I'm attempting to delete rows from Table_A based on a
} > criteria
} > that requires the presence of Table_B.
} >
} > Anyone out there have any ideas?????
} >
} > Thanx In Advance...
} >
} > Tony P.
}
} --
} http://www.tucson.com/objectware
} database design, reporting, query, demo tools and freebees
}
--
=========================================================================
= Tracy W. Nedd =
= Banctec Inc. - DBA =
= Silver Spring, Md =
= tracyn@banctec.clark.net or tracyn@asd.banctec.com =
=========================================================================
= ALL RIGHTS RESERVED. =
= This posting is copyright and may be used only for the purposes =
= of professional study, or the professional exchange of information. =
= Use of this posting by any commercial organization for the purposes =
= of vilifying another is explicitly prohibited. =
=========================================================================