Ok, so here is what I'm trying to do. If I have a duplicate client record I want to change the client_id in our tblClientCodes table where the client_id is the ID of the duplicate that we will remove at some point. All this I can do.
My problem is this, the two client records might have some of the same client codes. so the entire update will fail if any primary key violations are found as the update is being executed. Some of the client codes may not be the same and I want the client_id changed on those. How can I get the update to skip the key violations and change the rows that should be changed?
There are two rows in our tblClient table that I'm trying to merge into one and then I will delete the RemoveClientID record. But the delete in not done here.
ALTER PROCEDURE [dbo].[UpdateMergeDuplicates]
@.KeepClientID int,
@.RemoveClientID int
AS
BEGIN
UPDATE tbOrgCodes
SET tblOrgCodes.Client_ID = @.KeepClientID
WHERE tblOrgCodes.Client_ID = @.RemoveClientID
END
I am confused by your question, is it duplicate records in tblClientCodes or tblOrgCodes that is the problem?
Are your Id column identity integer or are they something else?
Please clarify and I will endeavour to answer Monday evening UK time.
|||This is an example of what might happen. A client has been entered into tblClient twice, so before removing the duplicate record, I need to change the client_id on the many side of the relationship between tblClient and tblOrgCodes. Both of the records in tblClients were assinged Org Codes. I want to simply change the client_id number for the rows assinged to the duplicate client record to the client_id that we will be keeping.
The problem is that both records could have been assigned some of the same codes, so my update statement will fail when it encounters a duplicate code. I want it to still update the codes that are not the same and skip the ones that are the same.
tblClient (1) ---- (m) tblOrgCodes
client(1234) non dup, has code1 and code2 in tblOrgCodes
client(4321) dup, has code1 and code3 in tblOrgCodes
I want to change the client_id for the code3 in tblOrgCodes from (4321) to (1234) and skip the code1 because it is a key violation.
This is a process so I will be doing it to other tables as well. But I need to no how to get the stored procedure to let me change the client_id of code3 and not terminate the statement. I tried the try/catch and it stopped the error, however, it did not update the code3 client_id.
@.KeepClientIDint, @.RemoveClientIDintASBEGINBEGIN TRYUPDATE tbOrgCodesSET tblOrgCodes.Client_ID = @.KeepClientIDWHERE tblOrgCodes.Client_ID = @.RemoveClientIDEND TRYBEGIN CATCH--?END CATCHEND|||
Does anyone have any idea how to do the try/catch here?
I appreciate any help,
|||
Jackxxx:
I want to change the client_id for the code3 in tblOrgCodes from (4321) to (1234) and skip the code1 because it is a key violation.
I did not understand how its a key violation? because its the same client_Id in reverse? or are there 2 records for client_id = 4321. Please post some more sample data..
|||There are two records for the same client, one was entered as a duplicate, thats the 4321. So I'm just changing the client_ID (this is NOT the key violation field) for records added to tblOrgCodes for client_id 4321 to client_id 1234. It is a code that is causing the key violation (code1). So as the sproc is doing the update it does something like this:
It finds a row in tblOrgCodes with the client_ID 4321 and tries to change it to 1234, if 1234 already has that code it causes a key viloation and terminates the statement. But there could be other codes in tblOrgCodes that need to be changed and do not cause a key violation. I need these to be updated even though a key violation was found on one of the previous rows.
I appreciate your help.
|||So is there a unique key on tbOrgCodes.Client_ID ?
You can have a secondary key without making it unique!
The key of the tblOrgCodes table is OrgCodelookup_ID and the Client_ID combined.
Normally I tell an update what to find in the where clause.
Is there a way to tell the update to skip any rows where the Client_ID and the OrgCodelookup_ID are the same?
|||The following should do it:
UPDATE tbOrgCodes
SET tblOrgCodes.Client_ID = @.KeepClientID
WHERE tblOrgCodes.Client_ID = @.RemoveClientID
AND tblOrgCodes.OrgCodelookup_ID NOT IN (SELECT OrgCodelookup_ID FROM tbOrgCodes
WHERE tblOrgCodes.Client_ID = @.KeepClientID)
However this hides the problem, a better way would to add a warning by using a @.WARNING INT OUPUT parameter
SET @.WARNING = 0
IF EXISTS(SELECT * FROM tblOrgCodes O1, tblOrgCodes O2 AND O1.OrgCodelookup_ID = Q2.OrgCodelookup_ID
AND O1.Client_ID = @.KeepClientID AND O2.Client_ID = @.RemoveClientID) SET @.WARNING = 1
ELSE
UPDATE tbOrgCodes|||
SET tblOrgCodes.Client_ID = @.KeepClientID
WHERE tblOrgCodes.Client_ID = @.RemoveClientID.
TATWORTH,
The subquery worked great. I have not been able to get the IF statement to clear syntax checking. Can you tell me what it will do and how it will effect the application?
Good things are always worth waiting for!
|||Try
IF EXISTS(SELECT * FROM tblOrgCodes O1, tblOrgCodes O2
WHERE O1.OrgCodelookup_ID = Q2.OrgCodelookup_ID
AND O1.Client_ID = @.KeepClientID AND O2.Client_ID = @.RemoveClientID) SET @.WARNING = 1
ELSE
UPDATE tbOrgCodes
SET Client_ID = @.KeepClientID
WHERE Client_ID = @.RemoveClientID
(this time I checked the syntax in Query Analyser)
Now what do you do with the warning? If you are going to delete the Client Record whose Id = @.RemoveClientID, then the following might be a better idea:
IF NOT EXISTS(SELECT * FROM tblOrgCodes O1, tblOrgCodes O2
WHERE O1.OrgCodelookup_ID = Q2.OrgCodelookup_ID
AND O1.Client_ID = @.KeepClientID AND O2.Client_ID = @.RemoveClientID) BEGIN
UPDATE tbOrgCodes
SET Client_ID = @.KeepClientID
WHERE Client_ID = @.RemoveClientID
DELETE Client WHERE id = @.RemoveClientID
END
This way you can action both table change in one stored procedure or even make it a transaction as in
IF NOT EXISTS(SELECT * FROM tblOrgCodes O1, tblOrgCodes O2
WHERE O1.OrgCodelookup_ID = Q2.OrgCodelookup_ID
AND O1.Client_ID = @.KeepClientID AND O2.Client_ID = @.RemoveClientID) BEGIN
BEGIN TRANSACTION
UPDATE tbOrgCodes
SET Client_ID = @.KeepClientID
WHERE Client_ID = @.RemoveClientID
DELETE Client WHERE id = @.RemoveClientID
COMMIT
END
|||
TATWORTH,
Again Thank You very much for explaining this and helping me. I look forward to giving it a try.
No comments:
Post a Comment