delete from mytable 
where team IN (  SELECT team from mytable 
	WHERE amount < 0 and option = "YES" )

>
>I have a mytable with 3 fields:
>
>team amount option

>AAAA  1000   NO
>AAAA  2000   YES
>AAAA  3000   NO
>AAAA  4000   NO
>
>BBBB  1000   NO
>BBBB  -2000  YES
>BBBB  3000   NO
>BBBB  4000   NO
>
>CCCC  1000   NO
>
>DDDD  2000   NO
>
>EEEE  8000   NO
>EEEE  -700   YES
>
>in order to have a mynewtable
>
>team  amount option
>
>AAAA  1000   NO
>AAAA  2000   YES
>AAAA  3000   NO
>AAAA  4000   NO
>
>CCCC  1000   NO
>
>DDDD  2000   NO

>the condition to delete all records in a group is when amount is negative AND
>option = YES .
>
>I try this but not work:, please assist to correct my SQL statment
>
>

>delete * from mytable where ( mytable.amount < 0 and option mytable.option = YES )
>

>
>in plain word when the amount is negative, delete that record or delete all records within the same group.
>
>TIA