-- 單價 Select nCM45_UNIT_PRICE, * from CM45020M where nCM45_Material_ID=171 -- 入庫數 出庫數 Select sum(nCM45_Stock_In), Sum(nCM45_Stock_Out) from CM45070M where nCM45_Material_ID=171 -- 進貨總價 Select sum(nCM45_Total_Price) from CM45070M where nCM45_Material_ID=171 -- 出貨總價 Select sum(nCM45_Total_Price) from CM45075M where nCM45_Material_ID=171 -- 單價 = 總價 / 數量 -- 單價 = (進貨總價 - 出貨總價) / ( 入庫數量 - 出庫數量) ------------------------------------------------------ update CM45020M set nCM45_UNIT_PRICE = Round(ISNULL( ( (Select ISNULL(Sum(nCM45_Total_Price),0) from CM45070M where nCM45_Material_ID=CM45020M.nCM45_Material_ID)- (Select ISNULL(sum(nCM45_Total_Price),0) from CM45075M where nCM45_Material_ID=CM45020M.nCM45_Material_ID) ) / (Select NULLIF(( sum(nCM45_Stock_In)- Sum(nCM45_Stock_Out)), 0) from CM45070M where nCM45_Material_ID=CM45020M.nCM45_Material_ID ) ,0), 2) 另一寫法 UPDATE Table SET Table.col1 = other_table.col1, Table.col2 = other_table.col2 FRO