[RESOLVED] I don't like RED (DELETE FROM TABLE WHERE COLOR = 'RED' .. but not all of them

[Axcontrols2]

Assume I have a table containing a list of my Clothes like this:

Item	Color
SHIRT	RED
SHIRT	BLUE
SHIRT	WHITE
PANTS	RED
PANTS	GREEN
HAT	RED
SHOES	RED
SHOES	BLACK
SHOES	BROWN
SOCKS	RED
COAT	BLUE

and now I don't like RED so I want to delete all the RED ones. This SQL would do that

DELETE FROM Wardrobe WHERE Color = 'RED'

But some of the items are only available in one color. For example I don't want to delete my HAT even though it's RED because I've only got one HAT.

So I want to delete all the red items except in cases where there's no alternative color.

This seems to work.

DELETE FROM Wardrobe WHERE Color = 'RED'
AND EXISTS (SELECT ITEM FROM Wardrobe AS CLOTHES WHERE CLOTHES.Item = Wardrobe.Item AND CLOTHES.Color  <> 'RED')

I figured it out by trial and error and that seems to work but I'm still nervous.

Is it reliable?
is there a better way?
Although it's just one line does it put a load on the server?
or what?

[szlamany]

Yes - this is an acceptable approach - one I would have probably used myself.

If this is MS SQL my syntax would have been

Delete From Wardrobe
   From Wardrobe WX
   Where WX.Color='RED' 
      and Exists(Select * From Wardrobe WX2 Where WX2.Item=WX.Item and WX2.Color<>'RED')

I use this syntax so I can comment out --Delete From Wardrobe and change it to SELECT * (for testing).

[Axcontrols2]

Thank you

[FunkyDexter]

The only downside I can see with that approach is that it uses a corelated subquery. If this is being run across very large volumes of data that can be a bit of a performance hog because it needs to be resolved for each row individually. Usually you will get a better result if you can express the query using a join:-

Create Table #Wardrobe (Item Varchar(8), Color Varchar(8))
 
Insert into #Wardrobe
Values  ('SHIRT','RED'),
        ('SHIRT','BLUE'),
        ('SHIRT','WHITE'),
        ('PANTS','RED'),
        ('PANTS','GREEN'),
        ('HAT','RED'),
        ('SHOES','RED'),
        ('SHOES','BLACK'),
        ('SHOES','BROWN'),
        ('SOCKS','RED'),
        ('COAT','BLUE')
 
 
Delete RedClothes
From #Wardrobe as RedClothes
Join #Wardrobe as NonRedClothes
    on RedClothes.Item = NonRedClothes.Item
where RedClothes.Color = 'RED'
And NonRedClothes.Color <> 'RED'
 
Drop Table #Wardrobe

I say usually because nothing is certain with the Query Engine. You might find that the sub query is actually better in your case so I'd suggest giving it a quick test with either approach.

[Axcontrols2]

@FunkyDexter

Do all DB's support that JOIN syntax? It's been a while since I worked with JET but I seem to remember it couldn't delete from a JOIN'ed table?

[FunkyDexter]

I don't think you mentioned the platform and, since you were talking about server performance, I guessed at SQL Server or MySQL, either of which would support it.

I quick Google search says that Access does not I'm afraid

[Axcontrols2]

I don't think you mentioned the platform and, since you were talking about server performance, I guessed at SQL Server or MySQL, either of which would support it.

I quick Google search says that Access does not I'm afraid

You're right. I didn't mention the platform - Sorry. I kind if forgot about jet till you mentioned that join syntax and it reminded me. My application uses IDBConnection and can connect to either MSSQL or MySQL so I try to use a common syntax with a function that fixes up things like "TOP n vs LIMIT n" etc. And, unfortunately, I still need to be able to connect to Jet for backward compatibility.

I'm trying to drop support for JET. As soon as I can do that I'll come back and look at that join syntax. Thanks.

[FunkyDexter]

Yeah, the delete from syntax isn't actually covered by the ANSII standard. Just about every current engine supports it... except JET. Kinda sucks but there you have it I'm afraid.