troubleshooting Question

Write and update statement from a select statement result set

Avatar of cookiejar
cookiejarFlag for United States of America asked on
Oracle Database
8 Comments1 Solution496 ViewsLast Modified:
Using this select statement, how can I transform into an update statement?
I would like to update the table ITEMS with the result returned from the result set of the following query:

select a.*,
       b.*,
       a.target - a.num_of_emp diff1,
       b.target - b.num_of_emp diff2,
       least(a.target - a.num_of_emp, b.target - b.num_of_emp) diff,
       b.num_of_emp - (least(a.target - a.num_of_emp, b.target - b.num_of_emp)) new_hardware_num_of_emp,
       b.num_of_emp + (least(a.target - a.num_of_emp, b.target - b.num_of_emp)) new_lumber_num_of_emp
  from items b,
       items a
 where a.name = 'LUMBER'
   and a.num_of_emp < a.target
   and b.name = 'HARDWARE'
   and b.code = a.code
   and b.num_of_emp > b.target;

I would like to
SET ITEMS.NUM_of_EMP to  new_hardware_num_of_emp where
    name = b.name and code = b.code
 and set  ITEMS.NUM_of_EMP to  new_lumber_num_of_emp where
    name = a.name and code = a.code
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros