0

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?

Juan Mellado
  • 14,973
  • 5
  • 47
  • 54
Brent Arias
  • 29,277
  • 40
  • 133
  • 234
  • An update statement can only affect a single table, so there is no way to update both sides of a join simultaneously. I gather you already know that, I just wanted to state it explicitly. The syntax in that web page you've dug up is not valid. Did you try it? – Aaron Bertrand Sep 09 '11 at 23:06
  • I'm a little perplexed by the question. If the 'Primary' account is defined as the oldest account associated with a customer, then you need only query for it. It sounds like you have chosen to denormalize the data for some unspecified reason. When I have implemented similar denormalized fields (for performance reasons) I've used triggers to maintain them. Whenever an account is updated or deleted a trigger would update, if needed, the PrimaryAccountId in the customer table. – HABO Sep 10 '11 at 02:58

2 Answers2

1

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

Community
  • 1
  • 1
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • You could also create a view, which you then update using a trigger, if you cannot refactor the base tables at this point in time. If you want an example, reply to this and I can post an answer. – David Manheim May 04 '12 at 18:29
0

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?

user937146
  • 228
  • 1
  • 7