11,645,499 members (74,920 online)
Rate this:
See more:
The query below works and gives me the most TopProfitted salesperson.

How can I modify the query below to include `UPDATE` statement to increase the salary of 12% ?

---Find the most TopProfited salesperson and increase the salary of that --salesperson 12%

```SELECT *
FROM (SELECT *
FROM (SELECT *
FROM (SELECT s.empid employeeid,
s.ename employeename,
SUM(i.price * oi.qty) - s.salary Topprofit
FROM salespersons s
LEFT JOIN orders o ON s.empid = o.empid
LEFT JOIN orderitems oi ON o.orderid = oi.orderid
LEFT JOIN inventory i ON oi.partid = i.partid
GROUP BY s.empid, s.ename, s.salary)
ORDER BY Topprofit DESC)
WHERE ROWNUM = 2
ORDER BY Topprofit ASC)
WHERE ROWNUM = 1 and Topprofit is not null;```
Posted 8-Feb-13 5:15am
7prince409
Edited 9-Feb-13 1:10am
v3
richcb at 8-Feb-13 11:22am

I would just use your salesperson query as the where clause for your update statement.

Rate this:

## Solution 1

Something like this.
Warning: My syntax may not be entirely correct
```Update salespersons set salary = salary * 1.12 where salespersons.empid =
(SELECT employeeid
FROM (SELECT *
FROM (SELECT *
FROM (SELECT s.empid employeeid,
s.ename employeename,
SUM(i.price * oi.qty) - s.salary Topprofit
FROM salespersons s
LEFT JOIN orders o ON s.empid = o.empid
LEFT JOIN orderitems oi ON o.orderid = oi.orderid
LEFT JOIN inventory i ON oi.partid = i.partid
GROUP BY s.empid, s.ename, s.salary)
ORDER BY Topprofit DESC)
WHERE ROWNUM = 2
ORDER BY Topprofit ASC)
WHERE ROWNUM = 1 and Topprofit is not null);
```
v4
7prince at 8-Feb-13 15:17pm

I ran the query and for some reason it say 0 row updated. Not sure why?

Update salespersons set salespersons.salary = salespersons.salary * 1.12 where salespersons.empid =
(SELECT salespersons.empid
FROM (SELECT *
FROM (SELECT *
FROM (SELECT s.empid employeeid,
s.ename employeename,
SUM(i.price * oi.qty) - s.salary Topprofit
FROM salespersons s
LEFT JOIN orders o ON s.empid = o.empid
LEFT JOIN orderitems oi ON o.orderid = oi.orderid
LEFT JOIN inventory i ON oi.partid = i.partid
GROUP BY s.empid, s.ename, s.salary)
ORDER BY Topprofit DESC)
WHERE ROWNUM = 2
ORDER BY Topprofit ASC)
WHERE ROWNUM = 1 and Topprofit is not null);
Mike Meinz at 8-Feb-13 15:49pm

Two things to try:

1. Your version is different than my version. In your version, the first SELECT is `SELECT salespersons.empid`. In my version, if is `SELECT s.empid`. I am not sure if that makes a difference or not.

2. I wonder if that first SELECT should be `SELECT employeeid` instead of `SELECT s.empid`. I notice now, that you rename the empid column in the innermost SELECT.
7prince at 8-Feb-13 23:13pm

Here is the latest revision: But, I am still getting 0 rows updated message.

Update salespersons set salespersons.salary = salespersons.salary * 1.12 where salespersons.empid =
(SELECT salespersons.empid
FROM (SELECT *
FROM (SELECT *
FROM (SELECT s.empid employeeid,
s.ename employeename,
SUM(i.price * oi.qty) - s.salary Topprofit
FROM salespersons s
LEFT JOIN orders o ON s.empid = o.empid
LEFT JOIN orderitems oi ON o.orderid = oi.orderid
LEFT JOIN inventory i ON oi.partid = i.partid
GROUP BY s.empid, s.ename, s.salary)
ORDER BY Topprofit DESC)
WHERE ROWNUM = 2
ORDER BY Topprofit ASC)
WHERE ROWNUM = 1 and Topprofit is not null);
7prince at 8-Feb-13 23:13pm

Here is the latest revision: But, I am still getting 0 rows updated message.

Update salespersons set salespersons.salary = salespersons.salary * 1.12 where salespersons.empid =
(SELECT salespersons.empid
FROM (SELECT *
FROM (SELECT *
FROM (SELECT s.empid employeeid,
s.ename employeename,
SUM(i.price * oi.qty) - s.salary Topprofit
FROM salespersons s
LEFT JOIN orders o ON s.empid = o.empid
LEFT JOIN orderitems oi ON o.orderid = oi.orderid
LEFT JOIN inventory i ON oi.partid = i.partid
GROUP BY s.empid, s.ename, s.salary)
ORDER BY Topprofit DESC)
WHERE ROWNUM = 2
ORDER BY Topprofit ASC)
WHERE ROWNUM = 1 and Topprofit is not null);
Mike Meinz at 9-Feb-13 7:09am

I am convinced that the first select should be `SELECT employeeid`. Neither of your attempts above included that. I have updated Solution 1.

If that still doesn't work, take the bundle of SELECT statements within the outermost parenthesis and run them in a query window within SQL Server Management Studio to be sure that EmployeeID is the result and that it is the correct value.