UPDATE语句使用JOIN

原文地址：http://blog.sina.com.cn/s/blog_648d306d0100r362.html



UPDATE语句使用JOIN (2011-05-28 09:53:46)转载▼
标签： 杂谈 分类： 软件开发
在触发器自动维护余额，即消费记录发生变动时自动汇总更新余额


1.加回原消费金额
UPDATE e SET e.money = e.money + d.amount
       FROM employee e
       INNER JOIN (SELECT empid,amount=sum(amount) FROM deleted  WHERE rechargeable=1  GROUP BY empid) d  ON d.empid=e.id
      
2.扣除新消费金额
UPDATE e SET e.money = e.money - i.amount
       FROM employee e
       INNER JOIN (SELECT empid,amount=sum(amount) FROM inserted WHERE rechargeable=1 GROUP BY empid) i ON i.empid=e.id