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
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 Apr 29 '16 at 8:13
  • my table name is dbo.Promotions – lel Apr 29 '16 at 8:14
  • 1
    Do you have multiple rows with "Membership Promotion", that have Y? – Rob Farley Apr 29 '16 at 8:15
  • oh my fault, there are duplicates of y in same promotion type. It works thanks! – lel Apr 29 '16 at 8:19
  • No worries. Happy to help. :) – Rob Farley Apr 29 '16 at 8:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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