SQL deLETE QUERY

This:

SELECT * FROM Product_Categories WHERE Product_ID = '101'

Returns me 2 items one where Category_ID = 0 and another where it is something different then 0

How would I delete all items in the table where  Category_ID = 0 IF there is at least one OTHER item where  Category_ID is NOT 0
vbnetcoderAsked:
Who is Participating?
 
knightEknightConnect With a Mentor Commented:
-- Run this as a SELECT first to make sure it only affects the rows you want to delete.  I also suggest you backup this table first.

select Product_ID
-- delete
from Product_Categories
where Category_ID = 0
  and Product_ID in (
         select Product_ID
         from Product_Categories
         group by Product_ID
         having count(*) > 1
  )

0
 
knightEknightCommented:
you can change the top line to SELECT * instead of SELECT Product_ID
0
 
jimyXCommented:
Delete from FROM Product_Categories where (Category_ID = 0) and (select count(Category_ID) from Product_Categories where Category_ID <> 0) > 1
0
 
vbnetcoderAuthor Commented:
ty
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.