delete all records with a same group

[ping99]

Hi all,

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 data after DELETE

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

 

[PHV]

Perhaps this ?
DELETE FROM mytable
WHERE team In (SELECT team FROM mytable WHERE amount<0 AND option='YES')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zion7]

Is Option a Boolean field or Text?
If text, then as PHV pointed out, surround with quotes.
If Boolean, Response is either (true or False) or (-1 or 0)