Do I need a cursor to get this job done in SQL?

Question

I have 3 tables, Movie, MovieRole, and Actor. MovieRole is a many to many table that relates movies and actors, but it also defines the role that the actor has on the table. Because it is possible for actors to have multiple roles within a movie, MovieRole is a table that has 3 columns:

MovieId int
ActorId int
RoleName varchar(100)

Instead of having a unique constraint on just MovieId and ActorId, we have it across all three fields. So there can't be a movie in which Eddie Murphy is in it and has the same role. But Eddie Murphy can be associated to the same movie twice if he has different role ("Professor Klump", "Buddy Love" in "The Nutty Professor").

The problem is we have a lot of duplicates in table Actor. So there could be a participant named "Edward Murphy" and another named "Eddie Murphy" and both of them could be associated to the same movie as "Professor Klump". And "Edward Murphy" might be associated to "Norbit" but not "Coming to America" and vice versa for "Eddie Murphy".

I need to create a stored procedure that merges actors and their movie role associations together. What it's supposed to do is send in two actor Ids, one being the primary actor record and one slated for delete. The movie role associations in the slated-for-delete actor have to be associated to the primary actor.

The stored procedure I have only works when the slated-for-delete actor doesn't have any role collisions with the primary actor. So if the s-f-d actor is in movie B and the primary actor is in movie A, it works out fine. Or if the s-f-d actor is in movie A but has a different role, we're all good. But if both actors are in the same movie with the same role, I run into that constraint.

How do I structure a query or stored procedure that addresses that contingency? I've been looking up ways to get this done and it seems like using a cursor would do it although everybody says to avoid using cursors whenever possible. Is there a way to accomplish what I want without using a cursor?

Right now, the basic gist of the sproc is like this:

alter procedure RoleMerge @ActorA int, @ActorB int as

update MovieRole set ActorId=@ActorA where ActorId=@ActorB

delete from Actor where Id=@ActorB

Answer 1

First of all delete rows which will violate the constraint if ActorId will be changed from @ActorB to @ActorA:

alter procedure RoleMerge @ActorA int, @ActorB int as
begin
    delete MovieRole
    from MovieRole as M
    where
        ActorId = @ActorB and
        exists (
            select *
            from MovieRole as M1
            where
                M1.ActorId = @ActorA and M1.MovieId = M.MovieId and
                M1.RoleName = M.RoleName
        )

    update MovieRole set ActorId = @ActorA where ActorId = @ActorB

    delete from Actor where Id = @ActorB
end