Click here to Skip to main content
Search within:



Next

modify query by adding UPDATE statement
Sign Up to vote bad
good
See more: Oracle
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:15
7prince394
Edited 9 Feb '13 - 1:10
André Kraak51.2K
v3
Add a Solution

Comments
richcb - 8 Feb '13 - 11:22
I would just use your salesperson query as the where clause for your update statement.

1 solution

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);
  Permalink  
Posted 8 Feb '13 - 5:28
Mike Meinz9K
Edited 9 Feb '13 - 1:06
v4
Comments
7prince - 8 Feb '13 - 15:17
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 - 8 Feb '13 - 15:49
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 - 8 Feb '13 - 23:13
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 - 8 Feb '13 - 23:13
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 - 9 Feb '13 - 7:09
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.
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 OriginalGriff 218
1 Sergey Alexandrovich Kryukov 159
2 Santhosh G_ 155
3 Richard MacCutchan 145
4 Maciej Los 136
0 Sergey Alexandrovich Kryukov 10,264
1 OriginalGriff 7,937
2 CPallini 4,201
3 Rohan Leuva 3,522
4 Maciej Los 3,135


Advertise | Privacy | Mobile
Web04 | 2.6.130523.1 | Last Updated 9 Feb 2013
Copyright © CodeProject, 1999-2013
All Rights Reserved. Terms of Use
Layout: fixed | fluid