更新表时Sql server查询错误 [英] Sql server query error while updating table

问题描述

执行sql查询时出现此错误



消息156，级别15，状态1，行2

关键字'Left'附近的语法不正确。

消息156，级别15，状态1，行6

关键字'where'附近的语法不正确。



我想更新m_Contractor的emp列，其中m_roller表的cen_Code，表表的cont_code是相等的。在m_Roller中，每个cen_Code，cont_Code都有很多记录。因此它应该按cen_Code，cont_Code分组，然后它应该更新到m_contractor表。



这里我发送的查询写的。



关于

sanjay thakker

I am getting this error while executing sql query

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'Left'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'where'.

I want to update emp column of m_Contractor where from m_roller table cen_Code, cont_code of the table tables are equal. In m_Roller there are so many no of records for each cen_Code,cont_Code. Hence it should be group by cen_Code,cont_Code after that it should be update into m_contractor table.

Herewith i am sending query which wrote.

with regards
sanjay thakker

update m_contractor
Left Join (
select count(*) as total_Emp,cen_Code, cont_Code
from m_roller
group by cen_code,cont_Code
where dp_Code=20 and pf_leave_date is null and grt_leave_Date is null and status is null) as  on m_contractor.cen_Code=t.cen_Code and m_contractor.cont_Code=t.cont_Code)
set m_contractor.emp = coalesce(t.total_Emp,0)

推荐答案

update m_contractor
set m_contractor.emp = coalesce(t.total_Emp,0)
Left Join (
select count(*) as total_Emp,cen_Code, cont_Code
from m_roller
where dp_Code=20 and pf_leave_date is null and grt_leave_Date is null and status is null
group by cen_code,cont_Code
) as t
on m_contractor.cen_Code=t.cen_Code and m_contractor.cont_Code=t.cont_Code

快乐编码！

：）

Happy Coding!
:)

这篇关于更新表时Sql server查询错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！