update erpordersheet a left join addressComp b
on a.NMemberID=b.HospID and a.AddID=b.AddressID
set a.ERPAddID=b.ERPAddressID where not (b.ERPAddressID is null)
on a.NMemberID=b.HospID and a.AddID=b.AddressID
set a.ERPAddID=b.ERPAddressID where not (b.ERPAddressID is null)
where a.NMemberID=b.HospID and a.AddID=b.AddressID and b.ERPAddressID is not null);
--补充一下楼上的,必须要限制一下主表被更新的记录的范围,
--如果没有限制,并且在b表中无匹配,则a表的相应值要被更新为空,
--可能不是你想要的!
UPDATE erpordersheet a
SET a.ERPAddID =
(SELECT b.ERPAddressID
FROM addressComp b
WHERE a.NMemberID = b.HospID AND
a.AddID = b.AddressID AND
b.ERPAddressID IS NOT NULL)
WHERE EXISTS (SELECT 1
FROM addressComp b
WHERE a.NMemberID = b.HospID AND
a.AddID = b.AddressID AND
b.ERPAddressID IS NOT NULL)
update t1 set (a,b)=(select c,d from t2 where t2.id=t1.id)
where exists(select 1 from t2 where t2.id=t1.id)