Update From Statement Example

/*資料更新回訂單主檔*/
UPDATE dbo.CN_Order_Header
SET DeliveryDate=ISNULL(hl.DeliveryDate,h.DeliveryDate),
Consignee=ISNULL(hl.Consignee,h.Consignee),
TelNum=ISNULL(hl.TelNum,h.TelNum),
DeliveryAddress=ISNULL(hl.DeliveryAddress,h.DeliveryAddress),
CheckAccount=ISNULL(hl.CheckAccount,h.CheckAccount)
FROM dbo.CN_Order_Header as h
JOIN dbo.CN_Order_Header_Log as hl
ON h.OrderID = hl.OrderID and
hl.ModifySNo = (SELECT MAX(ModifySNo)
FROM dbo.CN_Order_Header_Log
WHERE OrderID=h.OrderID)
and hl.ModifyStatus = ‘1′
and hl.OrderID=@OrderID
/*資料更新回訂單明細檔,可一次更新多筆單身*/
UPDATE dbo.CN_Order_Detail
SET ProductID=ISNULL(dl.ProductID,d.ProductID),
Unit=ISNULL(dl.Unit,d.Unit),
Qty=ISNULL(dl.Qty,d.Qty),
UnitPrice=ISNULL(dl.UnitPrice,d.UnitPrice),
Memo=ISNULL(dl.Memo,d.Memo)
FROM [...]

Continue reading »