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.num_of_emp diff1, - b.num_of_emp diff2,
       least( - a.num_of_emp, - b.num_of_emp) diff,
       b.num_of_emp - (least( - a.num_of_emp, - b.num_of_emp)) new_hardware_num_of_emp,
       b.num_of_emp + (least( - a.num_of_emp, - b.num_of_emp)) new_lumber_num_of_emp
  from items b,
       items a
 where = 'LUMBER'
   and a.num_of_emp <
   and = 'HARDWARE'
   and b.code = a.code
   and b.num_of_emp >;

I would like to
SET ITEMS.NUM_of_EMP to  new_hardware_num_of_emp where
    name = and code = b.code
 and set  ITEMS.NUM_of_EMP to  new_lumber_num_of_emp where
    name = and code = a.code

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
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