SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Table1 (
  ID int primary key,
  t1col2 varchar(8) not null,
  t1col3 varchar(8) not null
);
    
INSERT INTO Table1 (ID, t1col2, t1col3) VALUES
 (123, 'Fname1', 'Lname1')
,(456, 'Fname2', 'Lname2')
,(789, 'Fname3', 'LnameAA')
;

CREATE TABLE Table2 (
  ID varchar(8) primary key,
  t2col2 varchar(8) not null,
  t2col3 varchar(8) not null,
  t2col4 varchar(8) not null
);

INSERT INTO Table2 (ID, t2col2, t2col3, t2col4)
VALUES
 (122, 'Fname1', 'Lname1', 'String1')
,(466, 'Fname2', 'Lname2', 'String2')
,(789, 'Fname3', 'Lname3', 'String3')
;

CREATE TABLE Table3 (
  ID int primary key,
  t3col2 varchar(8) not null
);
    
INSERT INTO Table3 (ID, t3col2) VALUES
 (122, 'querty')
,(789, 'asdfgh')
;
8 rows affected

CREATE TABLE Table4(
  ID int primary key,
  t1col2 varchar(8) not null,
  t2col3 varchar(8) not null,
  t2col4 varchar(8) not null,
  t3col2 varchar(8) not null
);
DELETE FROM Table4;
INSERT INTO Table4 (ID, t1col2, t2col3, t2col4, t3col2)
SELECT t2.ID, t1.t1col2, t2.t2col3, t2.t2col4, t3.t3col2
FROM Table2 t2
JOIN Table1 t1 ON t1.ID = t2.ID AND t1.t1col3 LIKE '%AA%'
JOIN Table3 t3 ON t3.ID = t1.ID;
1 rows affected

SELECT * FROM Table4;


ID
t1col2
t2col3
t2col4
t3col2


789
Fname3
Lname3
String3
asdfgh


UPDATE t1
SET t1col3 = t4.t2col3
FROM Table1 t1
JOIN Table4 t4 ON t4.ID = t1.ID
WHERE t1.t1col3 != t4.t2col3;
1 rows affected

SELECT * FROM Table1;


ID
t1col2
t1col3


123
Fname1
Lname1

456
Fname2
Lname2

789
Fname3
Lname3