CODEWITHSUNDEEP
sqlsql-server
TickleMonster
TickleMonster

Reputation: 107

Update table column using sum()

I am trying to do two thing with my SQL code. I want it to copy data from another column (hours) , that’s located in another table (works- table), and place it into a new column (numHours) in a different table (Employee- table). However, I want it to use the sum of the hours worked for each employee id (eid) and then place that sum into the new column.

This is what I wrote, but two things are wrong. When I execute just the select statement every employee id has the same number of hours worked. When I run the whole statement I get this error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

UPDATE Employee
SET numHours= (SELECT sum(w.hours) AS totalHours From works w, Employee e WHERE
numHours IS NULL AND e.eid = w.eid Group by w.eid);

These are my tables

CREATE TABLE Employee(
eid INT,
ename VARCHAR(30),
age INT,
salary INT,

CONSTRAINT Pk_key_eid PRIMARY KEY (eid)
);

CREATE TABLE Department(
did INT,
dname VARCHAR(30),
budget int,
managerid INT,

CONSTRAINT Validate_managerid CHECK(managerid < 1000), 
CONSTRAINT Pk_key_did PRIMARY KEY (did)
);

CREATE TABLE Works(
eid INT,
did INT,
hours INT,

CONSTRAINT fk_key_eid FOREIGN KEY (eid) REFERENCES Employee (eid) ON DELETE CASCADE,
CONSTRAINT fk_key_Did  FOREIGN KEY (did) REFERENCES Department (did) ON DELETE CASCADE
);

How would I properly code this?

Upvotes: 0

Views: 67

Answers (2)

Raging Bull
Raging Bull

Reputation: 18747

Use JOIN and UPDATE:

UPDATE E
SET E.numHours=H.totalHours 
FROM Employee E JOIN
    (Select sum(hours) AS totalHours,eid  
     From works  
     Group by eid)H on H.eid=E.eid
WHERE E.numHours IS NULL

Explanation:

Inner query will select Total hours for each employee. Then use it to update Employee table.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269933

I believe you want this:

UPDATE Employee
    SET numHours= (SELECT sum(w.hours) From works w WHERE employee.eid = w.eid)
    WHERE numHours IS NULL;

Notes:

  • The condition on numHours belongs in the UPDATE, not the subquery.
  • You want a correlated query, not a full join (and don't use commas in the FROM clause ever!).
  • There is no need for a column alias in the subquery.
  • There is no need for GROUP BY in the subquery.

Upvotes: 1

Related Questions