Update column in table2 based on select query which contains count() from table1 using postgresql-CodePudding

Tables

Need to update department table's dcount column based on number of employees working in each department from employee table's dno column. Tried using update department set dcount=(select count() from employee INNER JOIN department ON employee.dno=department.dnumber group by dno);* which gave an error : more than one row returned by a subquery used as an expression

Desired result is:

**dname|dnumber|dcount

Research|5|4

Admin|4|3

Headquarters|1|1**

Need help please. Thanks in advance. Gruheeth

CodePudding user response：

Your subquery (select count() ...) returns several rows, one per employee, where postgres expect only one row from this subquery in order to update one row at a time in the department table. In this case, you case use a cte instead :

WITH list AS
(
select dno, count(*) AS dno_count
  from employee
 group by dno
)
update department AS d
   set dcount = l. dno_count 
  from  list AS l 
 where d.dnumber = l.dno ;