根据Id将行从一个表更新到另一个表

问题描述：

这两个表都有一个client_id列。当client_id列相同时，需要将#LocalDashboardtable中的数据插入到T004_Dashboard中。我已经试过，但它简化版，帮助显示错误根据Id将行从一个表更新到另一个表

update T004_Dashboard set T004_Dashboard.[GrossCharge],T004_Dashboard.[NetCharge] 
= (select #LocalDashboardtable.[GrossCharge] , #LocalDashboardtable.[NetCharge] 
from #LocalDashboardtable where 
#LocalDashboardtable.client_id =T004_Dashboard.client_id and 
#LocalDashboardtable.[month] =T004_Dashboard.[month] 
and #LocalDashboardtable.[year] =T004_Dashboard.[year] )

请帮我

您正在使用SQL Server的语法，但问题是标签的MySQL合格的列名。你真的在使用哪个数据库？ – 2014-12-04 03:44:10

我正在使用SQL Server2008 R2 – 2014-12-04 03:49:45

答

这是您的查询（这看起来很像SQL服务器）“附近有语法错误‘’。”：

update T004_Dashboard 
    set T004_Dashboard.[GrossCharge], 
     T004_Dashboard.[NetCharge] = (select #LocalDashboardtable.[GrossCharge], #LocalDashboardtable.[NetCharge] 
             from #LocalDashboardtable 
             where #LocalDashboardtable.client_id = T004_Dashboard.client_id and 
              #LocalDashboardtable.[month] = T004_Dashboard.[month] and 
              #LocalDashboardtable.[year] = T004_Dashboard.[year] 
            );

您无法将一对列设置为子查询中的一对列。相反，使用join：

update T004_Dashboard 
    set GrossCharge = ld.GrossCharge, 
     NetCharge = ld.NetCharge 
    from T004_Dashboard d join 
     #LocalDashboardtable ld 
     on ld.[month] = d.[month] and ld.[year] = d.[year] and 
      ld.client_id = d.client_id;

此外，SQL Server不允许在update/set声明

根据Id将行从一个表更新到另一个表

相关推荐