1

I have a table like below that's used to track contact information for our vendors. The 'IsPrimary' flag is normally supposed to be AP but another email can be set as the primary if we don't have the AP email or if another email was the first record added.

I would like to clean this up, so that -

  1. For any vendors with more than one record, set the 'IsPrimary' value to '1' for the row that has EmailType value of 'AP' (there will not be more than one record per EmailType)

  2. For any vendors with only one record, make sure the 'IsPrimary' flag is set to 1 for whatever email they have

  3. No vendor has more than one record with the 'IsPrimary' value of 1

How can I accomplish this? It would be even better if I can automate it, so that it can run every night but worst case scenario, I am OK running it manually every day.

VendorEmailID VendorID EmailType Email IsPrimary
1 1 AP ap@vendor.com 1
2 2 AP ap@vendor2.com 1
3 3 Sales sales@vendor3.com 1
4 4 AP ap@vendor4.com 1
5 2 Sales sales@vendor2.com 0
6 4 Billing billing@vendor4.com 0
7 5 Billing billing@vendor5.com 1
8 6 AP ap@vendor6.com 1
1

1 and 2 can be achieved by implementing this logic in a trigger on the table or a script that is run to assign 'IsPrimary' for existing records and then periodically (hourly / daily / etc) thereafter.

However steps 1 and 3 seem inconsistent:

set the 'IsPrimary' value to '1' for rows that have EmailType value of 'AP'

No vendor has more than one record with the 'IsPrimary' value of 1

Can each vendor record have more than one Email with an EmailType of 'AP'? If yes then how would you assign the 'IsPrimary' value?

1
  • I should've been clearer but there can only be one row per EmailType, so there won't be a scenario where one vendor has more than 1 email with EmailType of 'AP'.
    – Telluride
    Sep 10 at 17:27
1

You can do points 1 and 2 with an updatable CTE:

WITH cte AS (
    SELECT *,
      rn = ROW_NUMBER() OVER (PARTITION BY VendorID
        ORDER BY CASE WHEN EmailType = 'AP' THEN 1 ELSE 2 END, VendorEmailID)
    FROM Email
)
UPDATE cte
SET IsPrimary = CASE WHEN rn = 1 THEN 1 ELSE 0 END;

To enforce point 3, you can use a filtered unique index

CREATE UNIQUE NONCLUSTERED INDEX UX_Email_IsPrimary
  ON Email (VendorID) INCLUDE (IsPrimary) WHERE (IsPrimary = 1);

Once that constraint is in place, you must set IsPrimary to 0 at the same time or before setting another one to 1 for the same VendorID.

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.