One-Shot Set-Logic-Only Evaluation (Improve This Query)

Each customer has several accounts. One of the accounts, the oldest, is labeled as 'Primary' and all the others are labeled as 'Secondary'. Every week I run a single update statement "ClassifyAccounts" that takes a collection of new accounts and evaluates them according to this rule.

However, if sometime later the Primary account is closed, I then need to re-evaluate the new Primary from the remaining Secondary accounts the customer has. I want to find a way to do this so that

1. it is handled from the same "ClassifyAccounts" update statement I already execute each week and
2. the re-evaluation is optimized 开发者_运维知识库so that a re-evaluation does not occur unless it needs to occur.

Under these constraints, wherein I'm trying to avoid code with branches (I'm attempting a purely set-based approach), I can only achieve goal #1. The closest I can get to goal #2 is, perhaps, to set a 'NeedsReEvaluation' flag on the customer record and have the "ClassifyAccounts" update statement select any accounts that either (a) are new, with a NULL classification or (b) have a 'NeedsReEvaluation' flag set.

If I use that last trick, it would be nice to reset the 'NeedsReEvaluation' in the self-same update statement, but doing so would mean updating both sides of a join simultaneously (account and customer tables). Is this reasonable? Any other ideas?

---

Normalize (further) the table. One way would be:

I suppose you have a Customer and an Account table in 1:n relationship. I also guess you have an IsPrimary flag in the Account table that is set to True for the primary account of a customer and False for all others.

Create a new PrimaryAccount table with:

PrimaryAccount
--------------
CustomerId
AccountId
PRIMARY KEY (CustomerId)
FOREIGN KEY (CustomerId, AccountId) 
    REFERENCES Account(CustomerId, AccountId)
    ON DELETE CASCADE

Then, update this table using the Account.IsPrimary flag.

You can then drop that flag and modify the ClassifyAccounts you specify in your question. It will only need to change (insert or update) the PrimaryAccount table.

When a Primary Account is deleted, it will be off course deleted from both tables and then the ClassifyAccounts can be called.

As a side effect, you will not be able to have a customer with 2 accounts set as primary, even by mistake.

---

If you want to keep the current structure, you could use a transaction. See this answer for an example: how-to-update-two-tables-in-one-statement-in-sql-server-2005

---

What about using an update trigger on your customer table that updates the 'NeedsReEvaluation' flag for the corresponding row(s) in your account table whenever the primary account value (however that is stored) in your customer table changes?