sql server - SQL Delete not gettng the results i need. subquery, outer join -

i have requirement meet , can't seem figure out how delete records database entry.

i have table gets updated daily structure

    id  name eventnum state      1      1        4      2      1        2      3      1        3      4      1        9      5   b    3        1 

due things out of control users allowed input same information, on nightly job when column "state" has entry of 9 delete rows of data have same name , eventnum (so id 1-4 delete).

in oracle can subquery in microsoft sql looks can pass 1 column in in statement. ideas?

you can either use correlated subquery or join subquery. might able use simple self-join (the last query listed).

delete mt     mytable mt inner join (select name, eventnum mytable [state] = 9) sq on     sq.name = mt.name ,     sq.eventnum = mt.eventnum 

or:

delete mt1     mytable mt1     exists     (         select *         mytable mt2                     mt2.name = mt1.name ,             mt2.eventnum = mt1.eventnum ,             mt2.[state] = 9     ) 

or:

delete mt2     mytable mt1 inner join mytable mt2 on     mt2.name = mt1.name ,     mt2.eventnum = mt1.eventnum     mt1.[state] = 9