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

[David FOXWELL <David.FOXWELL@xxxxxxxxx>]

> -----Message d'origine-----
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de Evan Harris
> Envoyé : mercredi 31 mars 2010 13:20
> À : 'Midrange Systems Technical Discussion'
> Objet : RE: SQL Delete from table where rows found in a join 
>
> Hi David
>
> It looks like you are SELECTing a list so you can then use it 
> as input to a join to the same table to delete the rows you selected ?
>
> Why not just DETE FROM t WHERE ... ?

Ok, you asked for it. Here's my select :

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 ='.'  


Now how do I delete ?!