I have a task where I need to increase each row value under 'SAL' by a 100. Starting with the first entry and up to the last one.
I have written the code to select the proper entries and sorted them, but cant find a way to increase the values of a 'SAL' column. I am using https://livesql.oracle.com/ sandbox to try and learn SQL
SELECT ROWNUM, T1.*, T2.DNAME, T2.LOC
FROM SCOTT.EMP T1, SCOTT.DEPT T2
WHERE T1.DEPTNO = T2.DEPTNO
AND DNAME = 'SALES'
AND JOB NOT LIKE 'MANAGER'
ORDER BY HIREDATE DESC
The last line is not working
SET SAL = SAL + (ROWNUM * 100)
What about this?
UPDATE (
SELECT ROWNUM AS R, T1.*, T2.DNAME, T2.LOC
FROM SCOTT.EMP T1
JOIN SCOTT.DEPT T2 ON T1.DEPTNO = T2.DEPTNO
WHERE DNAME = 'SALES'
AND JOB NOT LIKE 'MANAGER'
ORDER BY HIREDATE DESC)
SET SAL = SAL + (R * 100);
Instead of ROWNUM AS R you can also use ROW_NUMBER() OVER (ORDER BY HIREDATE DESC) AS R (then you don't need the ORDER BY HIREDATE DESC)