CODEWITHSUNDEEP
mysqlsqlphpmyadmin
user3578048
user3578048

Reputation: 15

Update record in sql table with a sum of values from other table

Im new to SQL. I have a table employee, it has fields eID and eLoad. In addition i have table ongoing_projects it has pID, eID (those two are primary) and eLoad. Im trying to insert a sum of all eLoads for each employee. I have an idea for pseudocode, but I cannot implement it. Any Ideas? Thank you! 

For each eID in table employee
DO
UPDATE `employee` SET eload=(
  SELECT SUM (eload) as eload
  FROM ongoing_projects 
);

Upvotes: 0

Views: 92

Answers (1)

user3714582
user3714582

Reputation: 1940

If I understood It, you would like to do something like:

UPDATE employee e
SET e.eLoad = (SELECT SUM(op.eLoad) FROM ongoing_projects op WHERE op.eID=e.eID);

It updates each row in employees.eLoad column with the sum of the ongoing_projects.eLoad where the ongoing_projects.eID=actual employee eID

Or if you would like to SUM employees.eLoad with the ongoing_projects eLoad then the query may look:

UPDATE employee e
SET e.eLoad = e.eLoad + (SELECT SUM(op.eLoad) FROM ongoing_projects op WHERE op.eID=e.eID);

Upvotes: 2

Related Questions