2

i am trying to set my defaults column Y or N based on membershipType column. There can be only one Y in defaults column based on the 3 different value in membershipType column. Other than that, others will be N. How do i do it?

ID  titlePromo   membershipType         defaults
--  ----------   --------------         ------
1    Promo 1     Membership Promotion      Y
2    Promo 2     Membership Renewal        Y
3    Promo 3     Membership Grad           Y
4    Promo 4     Membership Promotion      N
5    Promo 5     Membership Promotion      N
6    Promo 6     Membership Grad           N

1 Answer 1

5

Use a unique filtered index.

CREATE UNIQUE INDEX uixDefault 
ON dbo.Promotions(membershipType)
WHERE defaults = 'Y';

This stops there being a second row with defaults='Y' of the same membershipType.

10
  • an error occur : The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Promotions' and the index name 'uixDefault'. The duplicate key value is (Membership Promotion ). The statement has been terminated.
    – lel
    Commented Apr 29, 2016 at 8:13
  • my table name is dbo.Promotions
    – lel
    Commented Apr 29, 2016 at 8:14
  • 1
    Do you have multiple rows with "Membership Promotion", that have Y?
    – Rob Farley
    Commented Apr 29, 2016 at 8:15
  • oh my fault, there are duplicates of y in same promotion type. It works thanks!
    – lel
    Commented Apr 29, 2016 at 8:19
  • No worries. Happy to help. :)
    – Rob Farley
    Commented Apr 29, 2016 at 8:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.