SQL query for salary increase

Question

I came up with following query which is not so challenging to resolve but still I think there might be better approach for this. Any suggessions?

I have got following table:

tb_Salary

EmpID DeptID Salary
---------------------
1  1  20000
2  1  20000
3  2  30000
4  2  800
5  2  200

I want to increase the salary of employees by Rs. 1000 only when the average salary in the department is more than 35000.

Is this possible using single update query?

I did this in following way. But seems that it is not that smart solution.

UPDATE  tb_Salary t1
SET  t1.Salary=t1.Salary+1000
WHERE  35000 < (select AVG(t2.Salary) from tb_Salary t2 WHERE t2.DeptID=t1.DeptID)

Answer 1

Your query is the way to go.

Answer 2

UPDATE  tb_salary
    SET tb_salary.salary = tb_salary + 1000
FROM    tb_salary
        INNER JOIN
        (SELECT   avg(salary) AS avg_salary,
                  deptID
         FROM     tb_salary
         GROUP BY tb_salary.deptID
         HAVING   avg(salary) > 10000) AS salary_increase
        ON salary_increase.deptID = tb_salary.deptID;    

I don't have time to test this, so it might not working. Usually I avoid multiple "Where" condition because it is not effective.