How to flag one record (row) as primary, if there are more than one record per ID?

Question

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	[email protected]	1
2	2	AP	[email protected]	1
3	3	Sales	[email protected]	1
4	4	AP	[email protected]	1
5	2	Sales	[email protected]	0
6	4	Billing	[email protected]	0
7	5	Billing	[email protected]	1
8	6	AP	[email protected]	1

Answer 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?

Answer 2

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.