Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jonneponne
Starting Member
5 Posts |
 Posted - 2006-08-15 : 05:15:28
|
| I've got a logical problem with my database when I want to do an update.For siplicity assume we only have 2 tables.Customers and InvoicesInvoices has CustomerID as FK.Now to my problem.It happends that the user register the same customer twice, hence I have two records in customer with the same customer (that have their own Invoices). Now I would like to merge these two into one record and cascade the update to Invoices. The problem then is that I will end up with the same PK in customer. I can't delete one record before updating its reletions. -> Catch 22.Whats the solution to this problem? I rather not change all the FK manually, I'd prefere a Update cascade/Delete action!Any suggestions?/Jonneponne |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-08-15 : 05:31:19
|
| First update all the invoices to the single Customer which you want to maitain in the table out of the two. Then Delete the other Customer informaton from the Customer table. if Possible try to implement the bussiness rules which can stop duplicating the customer informaton.This may help you in the future.Chirag |
 |
|
|
jonneponne
Starting Member
5 Posts |
Posted - 2006-08-15 : 05:39:22
|
| Tahnks chiragkhabariaI just thought this would be a common problem and could be solved with update cascade / delete. I assume your solution will work the only thing is that in my case there's many tables that will be affected and a was hoping that cascade would save me some time.Jonneponne |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-08-15 : 06:14:25
|
| I dont think, CASCADE Delete/Update will help you in this case. But you can write down the Generic Stored Procedure for updating all thecustomer ids from the different tables, this can be helpful to you in the future use also.Chirag |
|
|
|
jonneponne
Starting Member
5 Posts |
Posted - 2006-08-15 : 07:15:19
|
| OKI'll try your approach within a transaction. Thanks |
|
|
|
|
|
|
On Update Cascade logical issue