Re: Need Help with this delete

Dominick LoGiudice wrote
> I need to delete from the database any records from the database with an
> effdt date <= '01-JAN-1995'

    delete from my_table
    where trunc(effdt) <= to_date('01-JAN-1995', 'dd-mon-yyyy');

If performance is an issue, and if an index on effdt is available, then use

    delete from my_table
    where effdt < (to_date('01-JAN-1995', 'dd-mon-yyyy') + 1);

or if you are sure that all time parts of effdt are set to 0:00:00 (midnight), then simply use

    delete from my_table
    where effdt <= to_date('01-JAN-1995', 'dd-mon-yyyy');

> If their only record is the <= '01-JAN-1995' is the only record they got
> How would I keep them ?

You want to delete NOTHING if no records > 01-JAN-1995 exist? Then use something like

    delete from my_table
    where trunc(effdt) <= to_date('01-JAN-1995', 'dd-mon-yyyy')     and 0 <

        ( select count(*)
          from my_table
          where trunc(effdt) <=  to_date('01-JAN-1995', 'dd-mon-yyyy')
        );

or, if this should be per emplid:

    delete from my_table t1
    where trunc(t1.effdt) <= to_date('01-JAN-1995', 'dd-mon-yyyy')     and 0 <

        ( select count(*)
          from my_table t2
          where t2.emplid = t1.emplid
          and trunc(t2.effdt) <=  to_date('01-JAN-1995', 'dd-mon-yyyy')
        );

Arjan. Received on Tue Apr 13 1999 - 14:19:49 CDT