Resolving compound key collisions on mass updates

Question

Let's say I have a table Managers with fields Id and Name. There is another table Accounts that has fields Id and Name. These two tables have their relationships defined in a many to many table ManagedAccounts which has a composite key of ManagerId and AccountId. So you can have multiple managers on a certain account, but there can't be the same manager on the account multiple times.

Now, I have a stored procedure called MergeAccounts that takes in a Manager Id and a list of Manager Ids in the form of a comma delimited varchar. It currently looks a lot like this:

create procedure MergeAccounts @managerId nvarchar(12), @mergedManagers nvarchar(max) as declare @reassignment nvarchar(max)

set @reassignment='update ManagedAccounts set ManagerId='+@managerId+' where ManagerId in ('+@mergedManagers+')'

exec sp_executesql @reassignment

Since two managers could be on the same account, it'll give me an error saying that I've violated the compound key I have on that table. How do I need to structure my code to simply delete any redundant rows without regards to order?

so is this resolved then? or would this be of help: http://stackoverflow.com/questions/1139050/how-to-ignore-duplicate-key-error-in-t-sql-sql-server — xQbert, Aug 29 '13 at 18:13

score 1 · Accepted Answer · answered Aug 29 '13 at 18:37

Change your dynamic SQL to delete any potential collisions first. Then do your update. Wrap it all in a transaction.

(BTW, I would avoid using dynamic SQL altogether by creating a table-valued function that returns a table from a comma-separated list... this is very useful and you can probably find a function like that already written if you google it)

set @reassignment='
BEGIN TRAN;

BEGIN TRY
    DELETE  m1
    FROM    ManagedAccounts m1
            JOIN ManagedAccounts m2 ON m1.AccountId = m2.AccountId
    WHERE   m2.ManagerId = ' + @managerId + '
            AND m1.ManagerId IN (' + @mergedAccounts + ')

    UPDATE ManagedAccounts SET ManagerId=' + @managerId + ' WHERE ManagerId IN (' + @mergedManagers + ')
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
END CATCH;';

Resolving compound key collisions on mass updates

1 Answers1