Synchronising a table's key field from one database to another

Question

I have 2 databases

Test1

Test2

and I have Author table in each database, I would like to sync AuthorId from Test1 to Test2.

In Test2 also there is another table ProductAuthor which has the foreign Key constraint on AuthorID in Author table.

What I am doing at the moment is

1. Drop the foreign key constraint

2. Update the both tables : Author and ProductAuthor

3. Add the foreign key constraint

    --remove the foreign key constraint
   
   ALTER TABLE [dbo].[ProductAuthor] DROP CONSTRAINT [FK_ProductAuthor_Author]
   
   Update Test2.dbo.ProductAuthor set AuthorId = (
   select distinct T1A.AuthorId from Test1.dbo.Author T1A 
   INNER JOIN Test2.dbo.Author T2A ON T1A.FirstName =  T2A.firstname COLLATE Latin1_General_CI_AS and T1A.LastName =  T2A.surname COLLATE Latin1_General_CI_AS
   where T2A.AuthorId = ProductAuthor.AuthorId
   ) where ProductAuthor.AuthorId = 6793
   
   Update Test2.dbo.Author set AuthorId = (
   select Distinct T1A.AuthorId from Test1.dbo.Author T1A 
   INNER JOIN Test2.dbo.Author T2A ON T1A.FirstName =  T2A.firstname COLLATE Latin1_General_CI_AS and T1A.LastName =  T2A.surname COLLATE Latin1_General_CI_AS
   where T1A.AuthorId = 106793) where Test2.dbo.AuthorId = 6793
   
   
   --add the foreign key constraint
   ALTER TABLE [dbo].[ProductAuthor]  WITH NOCHECK ADD  CONSTRAINT [FK_ProductAuthor_Author] FOREIGN KEY([AuthorId])
   REFERENCES [dbo].[Author] ([AuthorId])
   

The above script updates a specific AuthorId from Test1.Author to Test2.Author and Test2.ProductAuthor

ASK:
How would I change the script so that it updates all the rows in Test2.Author and Test2.ProductAuthor based on the First Name and last Name in Test1.Author?

Answer 1

I think what you're trying to do is an update and select as found here:

How to do 3 table JOIN in UPDATE query?

You'll possibly need to just do a basic join - i.e.

JOIN (SELECT * FROM db1.author WHERE ....) as db1
SET db2.author = db1.ID

Try that and see if it works.