来自另一个表的SQL更新 - 大规模搞砸

Question

我有一个SQL表，需要根据应用程序表单的结果进行更新。

但是，我错过了 where 子句中最简单的内容，并设法覆盖了所有表数据。幸运的是，我们能够恢复这些信息，但是，我发出了嘘声，现在需要修复它并使其更健壮，这就是我需要帮助的地方。

UPDATE VETTING
SET EmploymentStatus = CASE
WHEN af.Company_Status = 2 THEN
0
ELSE
CASE
WHEN af.Company_Status = 1
OR af.Company_Status = 4 THEN
1
ELSE
NULL
END
END, 
CompanyLtdName = @CompanyId,
CompanyRegistrationNo = af.Company_Number,
VATRegistered_Ind = CASE
WHEN af.boolVATRegistered = 0
AND
(
af.VATNumber IS NOT NULL
AND af.VATNumber <> ''
) THEN
1
ELSE
0
END,
VATRegistration = CASE
WHEN af.VATNumber IS NULL
OR af.VATNumber = '' THEN
NULL
ELSE
SUBSTRING(
REPLACE(af.VATNumber, ' ', ''),
PATINDEX('%[0-9]%', REPLACE(af.VATNumber, ' ', '')),
LEN(REPLACE(af.VATNumber, ' ', ''))
- PATINDEX('%[0-9]%', REPLACE(af.VATNumber, ' ', ''))
)
END,
NoCCJs = af.App_HasCCJ,
NoCCJsDetails = af.App_HasCCJ,
NoDrugs = af.App_HasPastConviction,
NoDrugsDetails = af.App_PastConvictions,
Work_24Hour_Ind = af.Service_24hr,
Work_Commercial_Ind = af.Service_Commercial,
Work_Domestic_Ind = af.Service_Domestic,
Work_FreeEstimates_Ind = af.Service_Estimates,
Work_FreeEstimatesExceptIns_Ind = af.Service_EstimatesExclude,
Cards_Accepted_Ind = af.Service_Credit,
Insurance_Work_Undertaken_Ind = af.Service_Insurance,
PLInsurance_Ind = af.Vet_HasPLI,
PLAmount = af.Vet_PLIAmount,
PLPolicyNo = af.Vet_PLINumber,
PLExpiry_Dt = af.Vet_PLIExpDt,
PLIsCombined = af.Vet_PLI_Employee,
PLCost = af.Vet_PLICost,
PLInsuranceCompanyName = af.Vet_PLIProvider,
ELAmount = af.Vet_ELIAmount,
ELPolicyNo = af.Vet_ELINumber,
ELExpiry_Dt = af.Vet_ELIExpDt,
ELCost = af.Vet_ELICost,
ELInsuranceCompanyName = af.Vet_ELIProvider,
ExperienceStartDate = af.Accred_ExperienceStartDt
FROM dbo.ApplicationForm af
WHERE af.Company_ID = @CompanyId;

我还需要使用 where 子句，其中审查表公司 ID 也与 @CompanyId 匹配，我有一个代码盲时刻，我认为只有一个和 Vetting.Company_Id = @companyId 子句应该这样做，但我今天迷路了。

任何和所有的帮助都非常感谢。

Answer 1

>你需要连接两个表：

UPDATE V
SET 
V.EmploymentStatus = ...
...
FROM VETTING V
INNER JOIN dbo.ApplicationForm af
ON af.Company_ID =V.Company_ID 
WHERE af.Company_ID = @CompanyId;