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
1.加回原消费金额
UPDATE e SET e.money = e.money + d.amount
2.扣除新消费金额
UPDATE e SET e.money = e.money - i.amount
前一篇:sock_raw