您不需要在Engineering表中为此设置单独的列，您只需要一个可以查询的视图

CREATE VIEW EngineeringSpeedControl
AS

SELECT
  e.Tag,
  SpeedControl = CASE WHEN i.Tag IS NULL THEN 'No' ELSE 'Yes' END
FROM dbo.Engineering e
LEFT JOIN dbo.Instrumentation i
    ON i.Tag = RIGHT(e.Tag, LEN(e.Tag) - 3)
   AND i.[Function] = 'SC';

不幸的是，由于表的设计很差，您需要对字符串进行一些操作。
理想情况下，您可以将Engineering.Tag列拆分为单独的部分，这样您就可以执行直接连接

LEFT JOIN dbo.Instrumentation i
    ON i.Tag = e.Tag
   AND i.[Function] = 'SC';

由于我不知道engineers中的Tag列是否总是相同的格式，因此我保留了查询，以便它可以具有类似的设计xxxxx-nnnnnnnn，中间带有减号。

UPDATE [dbo].[Engineering] 
SET [Speed Control] =
CASE WHEN EXISTS ( SELECT 1 FROM [dbo].[Instrumentation] i WHERE RIGHT([dbo].[Engineering] .[tag],CHARINDEX('-', (REVERSE([dbo].[Engineering] .[tag]))) - 1) = CAST(i.[Tag] AS VARCHAR(10))) then 'YES' ELSE 'NO' END
WHERE [Speed Control]  IS NULL

结果将是

创建函数并在计算列中使用函数

create table instrumentation([Function]  varchar(200) null,  Tag varchar(200) null)  

  insert into instrumentation values('SC', '1234'),('SC', '1235'),('SC', '1237')

 create Function fn_Speed (@tag varchar(200))
 returns varchar(200)
 as
begin

declare @tagg varchar(200)= (select SUBSTRING(@tag, charindex('-', @tag)+1,10))
declare @result varchar(200)
--return  @tagg

If exists (
select  1 from instrumentation where tag =@tagg)
    select @result= 'True' 
else 
    select @result= 'False' 
return @result
end

  Create table engineering (tag varchar(200), Speed   as dbo.fn_Speed (tag) )

  insert into engineering(tag)values('PC-1234'),  ('ME-1235'), ('BF-1236')