Sqlserver update 和 delete 案例

在数据库中的简单更新：

update 表名 set 字段名='值' where id='123'

update jserp.somx set somx_poid='4553561524' where soid='SO240712049'

多表联合查询更新：

update  [NEWDBERP_Test2].JSERP.WOBOM  
    set WOBOM_XQSL=a3.ActualReqQty,
        WOBOM_WLID=dbo.GetWLIDfromU9(a4.Code),
        u_wobom_faswlid=a4.Code
from 
ERP.welm.dbo.MO_MO a2 ,
ERP.welm.dbo.MO_MOPickList a3 ,
ERP.welm.dbo.CBO_itemmaster a4     
    where 1=1
    and WOBOM_WOID = a2.docno
    and A2.ID =A3.MO AND WOBOM_BOMXH = a3.DocLineNO
    and a3.itemmaster = a4.ID
    and   a2.ModifiedOn >=dateadd(day,-2,GETDATE())
    and (dbo.GetWLIDfromU9(a4.Code)<>wobom_wlid or a3.ActualReqQty<> WOBOM_XQSL)
    and WOBOM_WOID not in ('MO23080420029','MO23082302082','MO23082401679','MO23082302152')

上表变形

update a1
    set a1.WOBOM_XQSL=a3.ActualReqQty,
        a1.WOBOM_WLID=dbo.GetWLIDfromU9(a4.Code),
        a1.u_wobom_faswlid=a4.Code
from 
[NEWDBERP_Test2].JSERP.WOBOM a1,
ERP.welm.dbo.MO_MO a2 ,
ERP.welm.dbo.MO_MOPickList a3 ,
ERP.welm.dbo.CBO_itemmaster a4     
    where 1=1
    and a1.WOBOM_WOID = a2.docno
    and A2.ID =A3.MO AND a1.WOBOM_BOMXH = a3.DocLineNO
    and a3.itemmaster = a4.ID
    and   a2.ModifiedOn >=dateadd(day,-2,GETDATE())
    and (dbo.GetWLIDfromU9(a4.Code)<>wobom_wlid or a3.ActualReqQty<> WOBOM_XQSL)
    and a1.WOBOM_WOID not in ('MO23080420029','MO23082302082','MO23082401679','MO23082302152')

错误案例：update 和 from 后面都有一样的表 jserp.somx，但是from后面联合查询得到的结果集和update后面的 jserp.somx没哟发生任何关系

update jserp.somx
    set U_SOMX_POKHWLID=b.somx_khwlid
from 
(select somx_soid,somx_soxh,somx_khwlid,somx_wlid from jserp.somx where somx_soxh like '999%' and SOMX_SJFHRQ >= '20240401' and SOMX_JHFHRQ >= '20240401') 
a,
(select a.DocNo somx_soid,'999'+convert(varchar(10),a2.DocLineNo) somx_soxh,a2.DescFlexField_PubDescSeg1 somx_khwlid
        from ERP.welm.dbo.SM_SO a 
        inner join ERP.welm.dbo.SM_SOLine a2 on a.ID =a2.SO    
        inner join ERP.welm.dbo.SM_SOShipline a3 on a2.ID =a3.SOLine
        left join ERP.welm.dbo.CBO_Operators_Trl a4 on a.Seller = a4.ID and a4.SysMLFlag='zh-CN'
        left join ERP.welm.dbo.Base_UOM_Trl a7 on a2.TU = a7.ID and a7.SysMLFlag='zh-CN'
        left join ERP.welm.dbo.CBO_Project a8  on a3.Project = a8.ID 
        left join ERP.welm.dbo.CBO_itemmaster a10 on a2.ItemInfo_ItemID = a10.ID
        left join (select b.EValue,b1.Name from ERP.welm.dbo.ubf_sys_extenumvalue b left join ERP.welm.dbo.UBF_Sys_ExtEnumValue_Trl b1 on b.id=b1.ID
         where ExtEnumTypeUID='833653D6-888D-406C-8CA3-D482AA115327') a19 on a19.EValue=a3.DemandType)
b 
    where a.somx_soid=b.somx_soid and a.somx_soxh=b.somx_soxh
    and len(b.somx_khwlid)>0
    and a.SOMX_KHWLID<>b.somx_khwlid

修改后：

update a
    set a.U_SOMX_POKHWLID=b.somx_khwlid
from 
jserp.somx a
,
(select a.DocNo somx_soid,'999'+convert(varchar(10),a2.DocLineNo) somx_soxh,a2.DescFlexField_PubDescSeg1 somx_khwlid
        from ERP.welm.dbo.SM_SO a 
        inner join ERP.welm.dbo.SM_SOLine a2 on a.ID =a2.SO    
        inner join ERP.welm.dbo.SM_SOShipline a3 on a2.ID =a3.SOLine
        left join ERP.welm.dbo.CBO_Operators_Trl a4 on a.Seller = a4.ID and a4.SysMLFlag='zh-CN'
        left join ERP.welm.dbo.Base_UOM_Trl a7 on a2.TU = a7.ID and a7.SysMLFlag='zh-CN'
        left join ERP.welm.dbo.CBO_Project a8  on a3.Project = a8.ID 
        left join ERP.welm.dbo.CBO_itemmaster a10 on a2.ItemInfo_ItemID = a10.ID
        left join (select b.EValue,b1.Name from ERP.welm.dbo.ubf_sys_extenumvalue b left join ERP.welm.dbo.UBF_Sys_ExtEnumValue_Trl b1 on b.id=b1.ID
         where ExtEnumTypeUID='833653D6-888D-406C-8CA3-D482AA115327') a19 on a19.EValue=a3.DemandType)
b 
    where a.somx_soid=b.somx_soid and a.somx_soxh=b.somx_soxh
    and len(b.somx_khwlid)>0
    and a.SOMX_KHWLID<>b.somx_khwlid
    and a.somx_soxh like '999%' and a.SOMX_SJFHRQ >= '20240401' and a.SOMX_JHFHRQ >= '20240401'

 

注意在Sqlserver中不能写成下面这种形式：update jserp.somx a set ... 

　　update jserp.somx a set ... 这种形式是尝试为表 jserp.somx 创建一个别名 a 并在更新语句中使用该别名。SQL Server 不支持直接在 UPDATE 语句中使用这种别名形式来引用表，因此会导致语法错误。

　　update a set ... from jserp.somx a,... 这种形式使用了 SQL Server 的 JOIN 语法，通过 FROM 子句将要更新的表与其他表连接起来。在这种情况下，可以为表 jserp.somx 创建别名 a，然后在 UPDATE 语句中使用这个别名来更新相应的列。

因此，第二种写法是正确的，因为它使用了 SQL Server 的 JOIN 语法，允许在 UPDATE 语句中使用别名来引用表。这种写法允许您从多个表中选择数据进行更新，并根据连接条件更新相应的列。

 

删除中的处理方式，通过 EXISTS 子查询结构，根据连接条件从表 JSERP.WOBOM 中删除符合条件的行（需要根据具体情况调整连接条件和子查询的逻辑）

delete from [NEWDBERP_Test2].JSERP.WOBOM 
where  EXISTS (
select * from 
(select b.code ,d.docno 
from ERP.welm.dbo.MO_MOModifyMOPickList a
left join ERP.welm.dbo.CBO_ItemMaster b on a.ItemMaster=b.id
left join ERP.welm.dbo.MO_MOModify c on a.MOModify=c.id
left join ERP.welm.dbo.mo_mo d on a.moid=d.id
left join ERP.welm.dbo.Base_Organization e on d.org=e.id
where convert(date,c.ApprovedOn)<=convert(date,getdate()) 
and convert(date,c.ApprovedOn)>=convert(date,DATEADD(day,-30,getdate())) 
and a.IsDelete=1) aa
where
 wobom_wlid=left(code,12)
and wobom_woid=aa.docno
)

--拆解:子查询结构（数据集）
select b.code ,d.docno 
from ERP.welm.dbo.MO_MOModifyMOPickList a
left join ERP.welm.dbo.CBO_ItemMaster b on a.ItemMaster=b.id
left join ERP.welm.dbo.MO_MOModify c on a.MOModify=c.id
left join ERP.welm.dbo.mo_mo d on a.moid=d.id
left join ERP.welm.dbo.Base_Organization e on d.org=e.id
where convert(date,c.ApprovedOn)<=convert(date,getdate()) 
and convert(date,c.ApprovedOn)>=convert(date,DATEADD(day,-30,getdate())) 
and a.IsDelete=1

-- 表 JSERP.WOBOM 和 子查询结构 的 连接条件：wobom_wlid=left(code,12) and wobom_woid=aa.docno