The main project is to copy all data from all tables WHERE AgencyID = 1 and INSERT them into the same tables WHERE AgencyID = 30. I did this for the 'Personnel' and 'Clients' tables. I am able to do this with a simple INSERT statement. The problem I am having is when I try to UPDATE the PrimaryProviderID in the clients table. Each client is assigned a Primary Provider.
PrimaryProviderID = Personnel.ID
Below is an UPDATE statement I have written to get the ID but it returns multiple rows. How can I fix this query to only get the ID of the row I want?
SET PrimaryProviderID = (SELECT p30.ID FROM Clients c INNER JOIN Personnel p1 ON c.PrimaryProviderID = p1.ID
INNER JOIN Personnel p30 ON p1.LastName = p30.LastName AND p1.FirstName = p30.FirstName)
WHERE AgencyID = 30