Re: rookie deleting records

Tim Downs <tim.downs_at_thedacare.org> wrote in message news:38BBDA62.F71565FC_at_thedacare.org...
>
>
> GHouck wrote:
>
> > Tim Downs wrote:
> > >
> > > I'm trying to delete records from a table based on another table and
> > > can't seem to get it to work. I can select the records I want to
delete
> > > using the following syntax but I can't seem to get the delete syntax
to
> > > work.
> > >
> > > select reqline.req_number, reqheader.req_number
> > > from reqline, reqheader
> > > where rewline.req_number = reqheader.req_number(+)
> > > and reqheader.req_number is null;
> > >
> > > tia
> > >
> > > Tim
> >
> > Unless I'm missing something ...
> >
> > Why are you testing
> >
> > reqheader.req_number = reqline.req_number
> > AND
> > reqheader.req_number is NULL ?
> >
> > If it is NULL, it's not going to be equal to
> > anything. Except, perhaps, another NULL.
> >
> > Typically, you can:
> >
> > delete from deltable DT where exists
> > ( select 'x' from tstTable TT where DT.key1=TT.key1 [and DT.key2=TT.key2
> > [and...]] );
> >
> > Yours,
> >
> > Geoff Houck
> > systems hk
> > hksys_at_teleport.com
> > http://www.teleport.com/~hksys
>
> What I have is reqheader table that contain the req_number and the reqline
> table that contains the req_number (will have mutiple reqlines to one
> reqheader). The program that purged the records out only removed the
> reqheader lines but not the reqlines from the reqline table. I need to
remove
> all the req_numbers from the reqline table that don't exist in the
reqheader
> table. The above select statement that I used returned the records that I
> need deleted.
>

Tim

try

delete from reqline
where req_number
not in
(select req_number
from reqheader)

Neil

(macaskil_at_liv.ac.uk) Received on Sat Mar 04 2000 - 23:55:33 CET