更新语句在SQL中失败

xyz 发表于 Dev

XYZ

下面的更新语句给我错误，请纠正我要去哪里的错误

消息102，级别15，状态1，行1
i附近的语法不正确

消息102，级别15，状态1，第20行
u附近的语法不正确

代码：

Update [dbo].[ManPlan_Prescriber_Roster] i  
set i.territory_no = u.territory_no,  
    i.key_sales_force = u.key_sales_force,  
    i.territory_type_id = u.territory_type_id,  
    i.territory_type_descr = u.territory_type_descr,  
    i.last_name = u.last_name,  
    i.first_name = u.first_name,  
    i.mi = u.mi,  
    i.territory_orig_assign_dt = u.territory_orig_assign_dt,  
    i.territory_strt_dt = u.territory_strt_dt,  
    i.email_address = u.email_address,  
    i.cell_phone = u.cell_phone,  
    i.fcl_addr1 = u.fcl_addr1,  
    i.fcl_city = u.fcl_city,  
    i.fcl_st = u.fcl_st,  
    i.fcl_zip = u.fcl_zip  
from 
    [dbo].[ManPlan_Prescriber_Roster] m  
join 
    (select 
         territory_no,  
         key_sales_force,  
         territory_type_id,  
         territory_type_descr,  
         last_name,  
         first_name,  
         mi,  
         territory_orig_assign_dt,  
         territory_strt_dt,  
         email_address,  
         cell_phone,  
         fcl_addr1,  
         fcl_city,  
         fcl_st,  
         fcl_zip 
     from 
         [dbo].[RepRosterUpd$]  
     where 
         filedt = '2016-05-18') u on m.territory_no = u.territory_no 
                                  and m.key_sales_force = u.key_sales_force
                                  and m.territory_type_id = u.territory_type_id

谢谢

marc_s

如果您要在from子句中使用要更新的表，并在该子句中为其提供别名（在此处：）m，那么您也必须在UPDATE语句中使用该别名：UPDATE m SET .....-您不能在表的顶部使用另一个别名“重新定义”该表你的UPDATE陈述

试试这个：

Update m              // use the alias you've defined on the FROM line!
set m.territory_no = u.territory_no,  
    m.key_sales_force = u.key_sales_force,  
    ......
    m.fcl_zip = u.fcl_zip  
from 
    [dbo].[ManPlan_Prescriber_Roster] m   // use this alias in UPDATE 
join 
    (select 
         territory_no,  
         key_sales_force,  
         .....
         fcl_zip 
     from 
         [dbo].[RepRosterUpd$]  
     where 
         filedt = '2016-05-18') u on m.territory_no = u.territory_no 
                                  and m.key_sales_force = u.key_sales_force
                                  and m.territory_type_id = u.territory_type_id