locked
How to use each row of a column to a WHERE clause in an UPDATE statement RRS feed

  • Question

  • User869176912 posted

    Hi all,

    I have a temp table that contains the following columns:

    DeptID   Department Name    TotalEmp

    I populate the Dept ID and Department Name by querying a table, I need to get the total count of all employee with similar department id, for each department. Employee info are on another table. And then update the temp table with the count

    How can I pass each row of the Dept ID in my temp table as a parameter to get the total number of employee.

    This is what I have

    UPDATE @tmp_tbl
    SET TotalEmp = COUNT(e.ID)
    FROM emptbl e
    INNER JOIN isActive a ON e.ID = a.ID
    WHERE e.dept = @tmp_tbl.DeptID //I want to pass each row on DEPT ID column here one at a time
    AND a.isActive = '1'

    Monday, January 29, 2018 11:25 PM

Answers

  • User347430248 posted

    Hi MikeT89,

    It looks like you have 2 tables, One for Employees and other for department.

    You want to insert data in third table called, Temp_Table.

    Try to refer example below.

    Department Table:

    Employee Table:

    Query:

    INSERT INTO temp_tbl( Deptid,Department_Name,TotalEmp )
    select d.Dept_id,d.dept_name,e.emp_cnt from dept_tbl d 
    join (select dept_id,count(emp_id) as emp_cnt from emp_tbl group by dept_id) e
    on d.Dept_id=e.dept_id

    Output:

    Temp Table:

    Further, You can modify the query based on your requirement.

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 30, 2018 1:23 AM
  • User77042963 posted
    SELECT dbo.tblDept.Id, dbo.tblDept.DeptName, isnulL(e.emp_cnt,0) emp_cnt
    FROM dbo.tblDept
    left JOIN (SELECT Dept_ID, COUNT(ID) AS emp_cnt 
    			FROM dbo.tblEmp
    			INNER JOIN dbo.tblActive
    			ON tblEmp.ID = tblActive.ID 
    			WHERE tblActive.Batch = '201706' 
    			GROUP BY Dept_ID) e 
    ON dbo.tblDept.Id = e.Dept_ID
    WHERE (((dbo.tblDept.Id) In ('P1','O1','M1','A1','C1','R1','P2','M1','M2','M3','M4','I1','I2','C2','P3','E1','E2')))
    ORDER BY LEN(dbo.tblDept.Id) ASC, dbo.tblDept.Id
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 30, 2018 6:16 PM

All replies

  • User347430248 posted

    Hi MikeT89,

    It looks like you have 2 tables, One for Employees and other for department.

    You want to insert data in third table called, Temp_Table.

    Try to refer example below.

    Department Table:

    Employee Table:

    Query:

    INSERT INTO temp_tbl( Deptid,Department_Name,TotalEmp )
    select d.Dept_id,d.dept_name,e.emp_cnt from dept_tbl d 
    join (select dept_id,count(emp_id) as emp_cnt from emp_tbl group by dept_id) e
    on d.Dept_id=e.dept_id

    Output:

    Temp Table:

    Further, You can modify the query based on your requirement.

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 30, 2018 1:23 AM
  • User869176912 posted

    It works! Thank you!

    Tuesday, January 30, 2018 4:24 PM
  • User869176912 posted

    So this partially works, but I just realized, it's not returning department that have zero employee count. I have a list of department that needs to be included whether there are employees or not. This is what I have:

    SELECT dbo.tblDept.Id, dbo.tblDept.DeptName, e.emp_cnt
    FROM dbo.tblDept
    INNER JOIN (SELECT Dept_ID, COUNT(ID) AS emp_cnt 
    			FROM dbo.tblEmp
    			INNER JOIN dbo.tblActive
    			ON tblEmp.ID = tblActive.ID 
    			WHERE tblActive.Batch = '201706' 
    			GROUP BY Dept_ID) e 
    ON dbo.tblDept.Id = e.Dept_ID
    WHERE (((dbo.tblDept.Id) In ('P1','O1','M1','A1','C1','R1','P2','M1','M2','M3','M4','I1','I2','C2','P3','E1','E2')))
    ORDER BY LEN(dbo.tblDept.Id) ASC, dbo.tblDept.Id
    

    Tuesday, January 30, 2018 5:40 PM
  • User77042963 posted
    SELECT dbo.tblDept.Id, dbo.tblDept.DeptName, isnulL(e.emp_cnt,0) emp_cnt
    FROM dbo.tblDept
    left JOIN (SELECT Dept_ID, COUNT(ID) AS emp_cnt 
    			FROM dbo.tblEmp
    			INNER JOIN dbo.tblActive
    			ON tblEmp.ID = tblActive.ID 
    			WHERE tblActive.Batch = '201706' 
    			GROUP BY Dept_ID) e 
    ON dbo.tblDept.Id = e.Dept_ID
    WHERE (((dbo.tblDept.Id) In ('P1','O1','M1','A1','C1','R1','P2','M1','M2','M3','M4','I1','I2','C2','P3','E1','E2')))
    ORDER BY LEN(dbo.tblDept.Id) ASC, dbo.tblDept.Id
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 30, 2018 6:16 PM
  • User869176912 posted

    I have the column default as zero, but after running this query it changes rows with no result to null instead of keeping it to zero. Is there a way I can make it keep the zero and not change it to NULL if there are zero employees?

    Tuesday, January 30, 2018 6:43 PM
  • User77042963 posted

    A left join with isnull should handle your case.

    Tuesday, January 30, 2018 7:26 PM