Update a column in table only if a condition is met for number of values for a field

[suhaas]

I am trying to figure out how to achieve the following. (This is a simplified version that ignores/ is stripped off non-relevant columns.)

I have a table Item with columns

item_id business_key

I have another table Order with columns

order_id business_key item_id

Here is the problem statement.

For a given item_id in Item table, find orders that have only one unique business_key associated with it in Order table.
Mind you, unique business_key could mean one or more rows for the unique combination of business_key and item_id. In this case, I need to take business_key derived from Order table and update the Item table with that value.

So if the Order table has values

Scenario I
Order Table

order_id business_key item_id
1 apple 12
2 apple 12

Then I need to update business_key in Item table with "apple"

Scenario II
However, if Order table has values

order_id business_key item_id
1 apple 12
2 orange 12

nothing needs to be done. business_key in Item table needs no update. (It is null by default.) . This is one time data fix. Going forward the relationship between Order and Item table will be handled by the application. In any case, business_key in Order table stays after conditional update to Item table.

What is the solution for this problem?

[LKBrwn_DBA]

I am trying to figure out . . .
What is the solution for this problem?

According to the sample data you posted, the results you stated are incorrect or incomplete.
Perhaps this is a better sample of what you want:

WITH Orders ( Order_Id, Business_Key, Item_Id )
     AS (SELECT 1, 'apple', 12 FROM DUAL UNION ALL
         SELECT 1, 'apple', 12 FROM DUAL UNION ALL
         SELECT 2, 'apple', 12 FROM DUAL UNION ALL
         SELECT 2, 'orange', 13 FROM DUAL)
--
-- This query selects the items to "add"
--
-- INSERT INTO Items --<< Un-comment this line and remove all previous lines.
SELECT o.Item_Id, o.Business_Key   
  FROM ( 
SELECT COUNT ( * ) Cnt
     , Order_Id
     , Item_Id
     , Business_Key
  FROM Orders
 GROUP BY Order_Id
       , Item_Id
       , Business_Key
  HAVING COUNT ( * ) = 1) O
 WHERE NOT EXISTS
          (SELECT '?'
             FROM Items I
            WHERE I.Business_Key = O.Business_Key
              AND I.Item_Id = O.Item_Id)
/