UPDATE [TestTable]
SET [HuoWei]='好'
FROM [TestTable] T1
WHERE NOT EXISTS
(SELECT 1 FROM [TestTable] T2
WHERE T1.[soCode]=T2.[soCode]
AND T2.[MoTypeId]=1
AND T2.[MoInQty]<T2.[MoQty])
SET [HuoWei]='好'
FROM [TestTable] T1
WHERE NOT EXISTS
(SELECT 1 FROM [TestTable] T2
WHERE T1.[soCode]=T2.[soCode]
AND T2.[MoTypeId]=1
AND T2.[MoInQty]<T2.[MoQty])
[soCode] varchar(50), 销售单号,一个销售订单会对应多个组装工单和多个金工工单
[MoQty] int , 工单数量
[MoInQty] int , 入库数量
[HuoWei] varchar(50) 物料状态
create proc update_HuoWei
as
declare @socode varchar(50)begin
update a set HuoWei='好'
from TestTable a
left outer join (select sum(MoInQty)MoInQty,soCode from TestTable where MoTypeId=1 group by soCode)b on a.soCode=b.soCode
left outer join (select sum(MoQty)MoQty,soCode from TestTable where MoTypeId=1 group by soCode)c on a.soCode=b.soCode
where a.MoTypeId=2 and isnull(b.MoInQty,0)>=isnull(c.MoQty,0)
end