sql-server - SQL Server 更新查询中的语法不正确

Question

以下更新查询有什么问题。错误信息是

“SD”附近的语法不正确。

代码：

UPDATE T_EmpSelf_Details SD, T_TravelDetails TD 
SET SD.Host_Country = TD.New_Host_Country 
WHERE 
    SD.EL_Year = TD.EL_Year 
    AND SD.Emp_ID = TD.EmpId 
    AND SD.Staff_number IN ('00002080', '00002320');

标签： sql-server

Answer 1

没有理由让事情变得过于复杂......不需要 CTE、派生表或子查询......

UPDATE SD SET
    SD.Host_Country = TD.New_Host_Country
FROM
    T_EmpSelf_Details SD
    JOIN T_TravelDetails TD
        ON SD.EL_Year = TD.EL_Year
        AND SD.Emp_ID = TD.EmpId
WHERE
    SD.Staff_number IN ('00002080', '00002320');

此语法还有一个额外的好处，即允许您将查询编写为 SELECT，验证您的谓词是否正确，然后轻松将其转换为 UPDATE...

/* begin by writing it as a SELECT... */
SELECT 
    SD.Host_Country, 
    TD.New_Host_Country
FROM
    T_EmpSelf_Details SD
    JOIN T_TravelDetails TD
        ON SD.EL_Year = TD.EL_Year
        AND SD.Emp_ID = TD.EmpId
WHERE
    SD.Staff_number IN ('00002080', '00002320');


/* when you're satisfied that the correct rows & values are in scope. convet it to an UPDATE...
-- begin by writing it as a SELECT... */

--SELECT 
--  SD.Host_Country, 
--  TD.New_Host_Country
UPDATE SD SET
    SD.Host_Country = TD.New_Host_Country
FROM
    T_EmpSelf_Details SD
    JOIN T_TravelDetails TD
        ON SD.EL_Year = TD.EL_Year
        AND SD.Emp_ID = TD.EmpId
WHERE
    SD.Staff_number IN ('00002080', '00002320');