Conditionally delete from a table? 1

[gbaughma]

I have 3 tables... "Products", "Categories", and "CategoryProduct"

Obviously, CategoryProduct has a one-to-many link between "Products" and "Categories".

I need to conditionally delete rows from CategoryProduct.

I guess just explaining what I need it to do is the easiest.

If a Product exists in CategoryProduct, and the CategoryID <> (whatever I need it to be), then delete the line.

IN other words:

I want to remove all lines in CategoryProduct where the ProductID for specific products exists in CategoryProduct, *UNLESS* the category = an arbitrary number.

So, if CategoryProduct looks like this:

ProductID CategoryID
1 10
1 11
1 12

.... and I want just "10" left in there, I need to delete the 2nd and 3rd lines.

Does that make sense?



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[simian336]

delete
from CategoryProduct
where ProductID = 1
and CategoryID <> 10

Simi

 

[gbaughma]

OK... I perhaps didn't explain well enough.

I need something more like

"DELETE FROM CategoryProduct WHERE ProductID=(SELECT ProductID FROM Products WHERE Description LIKE 'TSHIRT%') AND CategoryID <> 10"



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[bborissov]

DELETE FROM CategoryProduct 
FROM CategoryProduct 
INNER JOIN Products 
      ON CategoryProduct.ProductID = Products.ProductID AND
         Products.Description LIKE 'TSHIRT%'
WHERE CategoryProduct.CategoryID <> 10

(not tested!)

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.