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 -
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)
For any vendors with only one record, make sure the 'IsPrimary' flag is set to 1 for whatever email they have
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 | 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 |