An example of using UPDATE with JOIN

UPDATE_with_JOIN_example.sql

USE TestDatabase

/* Create tables */
CREATE TABLE [dbo].[TParent](
[id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[name] [nvarchar](300) NULL
)

CREATE TABLE [dbo].[TChild](
[id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[name] [nvarchar](300) NULL,
[parent_id] [int] NOT NULL FOREIGN KEY REFERENCES [dbo].[TParent](id)
)

/* Filling data (incomplete for the child table */
INSERT INTO [dbo].[TParent] (name)
VALUES ('Record_1'),
	     ('Record_2')

INSERT INTO [dbo].[TChild] (name, parent_id)
VALUES ('Record_1_child ', 1),
	     (NULL, 2)

/* Update the child table rows with data missing */
UPDATE TC
SET TC.name = TP.name + '_child'
FROM [dbo].[TChild] TC
LEFT JOIN [dbo].[TParent] TP on TC.parent_id = TP.id
WHERE TC.name IS NULL