求sqlserver高手指导

UPDATE C_M set C_M.amount = amount - Exper1
WHERE (C_M.mname= Expr2)
 
SELECT C_DR.count * C_OR.dcount AS Expr1, 
      C_DR.mname AS Expr2
FROM C_D INNER JOIN
      C_DR ON C_D.DID = C_DR.did INNER JOIN
      C_OR ON C_D.DName = C_OR.dname INNER JOIN
      C_M ON C_DR.mname = C_M.mname
WHERE (C_OR.oid = 'ODF0120808_0001')
ORDER BY C_DR.mname
 四张表
C_M(mname,amount)
C_D(DID,DName)
C_DR(did,mname,count)
C_OR(oid,dname,dcount)
1通过已知的oid，查找到OR表下dname的个数dcount，通过dname查找到DR表下mname,和count。分类求Expr1。
2更新M表，根据查询到的Mname和Expr1。更新相应amount值
这2条sql语句如何放在一起用啊，存储过程或者触发器，或者视图都可以，哪位大神教我下怎么合并起来啊。

补充：测试后：无法绑定由多个部分组成的标识符“C_DR.mname”

答案：UPDATE C_M set C_M.amount = amount - tt.Exper1
from (
      SELECT C_DR.count * C_OR.dcount AS Expr1, 
                    C_DR.mname AS Expr2
      FROM C_D INNER JOIN
          C_DR ON C_D.DID = C_DR.did INNER JOIN
          C_OR ON C_D.DName = C_OR.dname INNER JOIN
          C_M ON C_DR.mname = C_M.mname
      WHERE (C_OR.oid = 'ODF0120808_0001')
      ORDER BY C_DR.mname
   )  as tt
WHERE (C_M.mname= tt.Expr2)

其他：update  C_M set C_M.amount = amount - Exper1 where
 
select c.count*c1 as Expr1.dcount from
                                                        (select select c.mname,c.count ,c1.dcount
                                                           from C_DR c inner join C_OR c1 
                                                                                       on c1.oid = 'ODF0120808_0001’ 
                                                                                            and c1.dname = c.mname) d   
你试试 UPDATE C_M set C_M.amount = C_M.amount -  aaa.Exper1 from  C_M , (SELECT C_DR.count * C_OR.dcount AS Expr1,        C_DR.mname AS Expr2 FROM C_D INNER JOIN       C_DR ON C_D.DID = C_DR.did INNER JOIN       C_OR ON C_D.DName = C_OR.dname INNER JOIN       C_M ON C_DR.mname = C_M.mname WHERE (C_OR.oid = 'ODF0120808_0001') ORDER BY C_DR.mname )  as aaa WHERE (C_M.mname= aaa.Expr2) 
 
这样试试，把两个表关联起来应该没什么问题。

上一个：sqlserver的查询语句？
下一个：sqlserver 包含一个时间区间的存储过程如何写