I have 2 tables Employees and department
Now I want to find department-wise the highest salary and then increment them by 2000
update emp set sal = sal + 2000 where empid in ( select empid from dept where (deptno,sal) in ( select d.deptno,max(e.sal) from emp e, dept d where e.empid = d.empid group by deptno));
ed: Hah It didn't occur to me that you actually meant update the rows, I was thinking you just wanted to retrieve the values. @Ajith Sasidharan's answer is the fuller one.
It seems odd that your department table references the employee table instead of the other way around, however this should do what you want:
SELECT d.deptno, d.deptname, max(e.salary+2000) FROM department d INNER JOIN employee e ON e.empid = d.empid GROUP BY d.deptno, d.deptname;