Updating a summary table based on 2 other tables

Commisions (commisionID INT, EmployeeID, amount INT, created Datetime)

Sales (saleID INT, EmployeeID, amount INT, created datetime)

The summary table:

Employee (employeeID, totalCommisions INT, totalSales INT, created DateTime)

There can be 0 or more rows per employee in both Commissions and Sales tables.开发者_开发知识库

Query#1 The query is to update the Employee table, summing the total commision into the totalCommision column for the employee, and the same for the totalSales.

Query#2 Same as above, but limit the total commission for a given day to $100. So if they earned $150 commission in a day, set the value to $100.

---

Try this:

UPDATE Employee 
SET    totalCommisions = Commisions.total,
       totalSales = Sales.total
FROM   ( SELECT employeeID, SUM(amount) AS total
         FROM Commisions
         GROUP BY employeeID ) AS Commisions,
       ( SELECT employeeID, SUM(amount) AS total
         FROM Sales
         GROUP BY employeeID ) AS Sales
WHERE  Employee.employeeID = Commisions.employeeID
AND    Employee.employeeID = Sales.employeeID

The second query you should try on your own.