Write and update statement from a select statement result set

You could try something like this:

UPDATE items a
   SET ( items.num_of_emp, items.num_of_emp)      =
         (SELECT 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
           WHERE b.name = a.name
             AND b.code = a.code)
 WHERE (a.name = 'LUMBER'
    AND a.num_of_emp < a.target)
    OR (a.name = 'HARDWARE'
    AND a.num_of_emp > a.target);

Select allOpen in new window

:p

I tried running this is said duplicate column name,
I am trying to update  one column.


UPDATE items a
   SET ( items.num_of_emp, items.num_of_emp)      =
         (SELECT 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
           WHERE b.name = a.name
             AND b.code = a.code)
 WHERE (a.name = 'LUMBER'
    AND a.num_of_emp < a.target)
    OR (a.name = 'HARDWARE'
    AND a.num_of_emp > a.target);

OK I see...
Perhaps this:

UPDATE items a
   SET items.num_of_emp      =
         (SELECT CASE
                   WHEN a.name = 'LUMBER'
                    AND a.num_of_emp < a.target
                   THEN b.num_of_emp
                     + (LEAST ( a.target - a.num_of_emp, b.target - b.num_of_emp)) --new_lumber_num_of_emp
                   WHEN a.name = 'HARDWARE'
                    AND a.num_of_emp > a.target
                   THEN b.num_of_emp
                     - (LEAST ( a.target - a.num_of_emp, b.target - b.num_of_emp)) --new_hardware_num_of_emp
                 END
            FROM items b
           WHERE b.name = a.name
             AND b.code = a.code)
 WHERE (a.name = 'LUMBER'
    AND a.num_of_emp < a.target)
    OR (a.name = 'HARDWARE'
    AND a.num_of_emp > a.target);

Select allOpen in new window

:p

>> This statement updates all of the records.
UPDATE items a
   SET items.num_of_emp      =
         (SELECT CASE
                   WHEN a.name = 'LUMBER'
                    AND a.num_of_emp < a.target
                   THEN b.num_of_emp
                     + (LEAST ( a.target - a.num_of_emp, b.target - b.num_of_emp)) --new_lumber_num_of_emp
                   WHEN a.name = 'HARDWARE'
                    AND a.num_of_emp > a.target
                   THEN b.num_of_emp
                     - (LEAST ( a.target - a.num_of_emp, b.target - b.num_of_emp)) --new_hardware_num_of_emp
                 END
            FROM items b
           WHERE b.name = a.name
             AND b.code = a.code)
 WHERE (a.name = 'LUMBER'
    AND a.num_of_emp < a.target)
    OR (a.name = 'HARDWARE'
    AND a.num_of_emp > a.target);

I would like to only update where a.name = LUMBER and target > num_of_emp
and where LUMBER code = HARDWARE code and HARDWARE num_of_emp > target.


I would like the select statement to return this result for example:

Name                       Code           Target         Num_of_emp                New_Num_of_emp

HARDWARE      6000      1      2                             1
LUMBER            6000      3      1                             2

The select statement would return this for example.  and I would like to update num_of_emp  in the items table where name = name in the resultset and code = code in the resultset .

Being a newbie to Oracle, the following is confusing

>>
MERGE  INTO ITEMS I
USING (
select
code
,CASE d.type
   WHEN 'A' then a_name
   ELSE
      b_name
   END name
,CASE d.type
   WHEN 'A' then  new_lumber_num_of_emp
   ELSE
      new_hardware_num_of_emp
   END new_num_of_emp
from  
(select 'A' type from dual
union
select 'B' type from dual
) d -- subquery to get 2 records
,
(
       insert your query replacing  
select a.*,
       b.*,
by
select a.name a_name, a.code, b.name b_name
) yourquery
) S  -- contains 2 records for each origal row with columns switched
ON (i.code = s.code and i.name  = S.name)
WHEN MATCHED THEN
UPDATE set NUM_of_EMP = s.new_num_of_emp
/

by: cookiejarPosted on 2013-07-08 at 19:40:28ID: 39309139
>> This statement updates all of the records.
. . .   E t c   . . .

Not true, look at the conditions:

UPDATE items a
   SET items.num_of_emp      =
. . .   E t c   . . .
 WHERE (a.name = 'LUMBER'
    AND a.num_of_emp < a.target)
    OR (a.name = 'HARDWARE'
    AND a.num_of_emp > a.target);

Select allOpen in new window

I can image the confusion : it is not an often used syntax
The merge operator makes it possible to do updates based on a query; it can do inserts, updates and deletes : but in this case only the update feature is used.

It updates those rows in table items where the on columns match the columns in the query and uses a value from the query to update the rows

You can execute the query the merge is based on:

select
code
,CASE d.type
   WHEN 'A' then a_name
   ELSE
      b_name
   END name
,CASE d.type
   WHEN 'A' then  new_lumber_num_of_emp
   ELSE
      new_hardware_num_of_emp
   END new_num_of_emp
from  
(select 'A' type from dual
union
select 'B' type from dual
) d -- subquery to get 2 records
,
(
select a.name a_name, a.code, b.name b_name,
       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
) yourquery

to check if this are the rows you want to change and if  the new value is the correct one

that  query depends of 2 datasets : 1 is the the result of your query , 2 is a trick to multiply the result by 2

the reason for the last is that you want to do 2 updates for each row in your query and the merge statement requires the identifiing value for each row to update.
By using the CASE d.type  it is possible to select the data from yourquery for each of the 2 rows in a different way