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

-----Message d'origine-----
[mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de Dennis Lovelady
Envoyé : mercredi 31 mars 2010 14:40
À : 'Midrange Systems Technical Discussion'
Objet : RE: SQL Delete from table where rows found in a join

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

Your statement is incomplete. In order to answer the
question, we need to see the rest of the WITH clause, and see
how T is used. (Is there another WHERE clause, et cetera.)

Also need to know from which table the delete shall take place, ...

Dennis, you've cut off the half of my sql statement.
Here's what I posted :

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.