RE: SQL Delete from table where rows found in a join

["Dennis Lovelady" <iseries@xxxxxxxxxxxx>]

> with t as (
> SELECT fld1, fld2, fld3, MAX(fld4) lmax FROM myfile GROUP BY
> fld1, fld2, fld3)
>
> select a.* from myfile a join t on
> a.fld1=t.fld1 and
> a.fld2=t.fld2 and
> a.fld3=t.fld3 and
> a.fld4=t.lmax
> join otherFile m on m.fld1= a.fld1 and m.fld2=a.fld2
>         where a.fld4 ='.'
>
>
>
> this selects the rows from myfile that I want to delete.

I did cut it off.  Sorry.

David, this is contrived, obviously since a.fld4 is both alpha and numeric.
Also unless you have a table named T, you've neglected to supply its name.
You will also have likely problems with your first join due to using fld4
instead of lmax (if you can even do that).

But in general, if all else were the same, I would do:
Delete from myfile a where exists
    (select 1 from t
     Join otherfile m on m.fld1=a.fld1 and m.fld2=a.fld2...
     Where t.fld1=a.fld1 and t.fld2=a.fld2 ...
     )

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"Millions long for immortality who do not know what to do with themselves on
a rainy Sunday afternoon."
        -- Susan Ertz