ORACLE: UPDATE using two tables, Concatenation

I have two tables involved in this query I need to create, and I'm not exactly sure how to join these two tables in order to update.

I have a ITEM and CONSUMER_ITEMS table. The ITEM table has a distinct code for each item and a UPC code. I need to concatenate a string with the ITEM.UPC_CODE to CONSUMER_ITEMS.NEW_ITEM_CODE where CONSUMER_ITEMS.ITEM_CODE = (Specific list of ITEM.ITEM_CODES)

How would I go about updating the CONSUMER_ITEMS.NEW_ITEM_CODE Field?

It would essentially be equal to 'string' || ITEM.UPC but how do I reference the CONSUMER_ITEMS.ITEM_CODE to be equal to the specific ITEM_CODE in the list of ITEM_CODES to be updated.

From stackoverflow

• Sounds like you want:

  UPDATE consumer_items ci
  SET    new_item_code = (SELECT 'string' || item.upc_code
                          FROM   item
                          WHERE  item.item_code = ci.item_code
                         )
  WHERE  ci.item_code IN ('a','b','c');
  

  Alternatively, assuming there is a foreign key relationship between the tables and that consumer_items has a primary key, this should work:

  UPDATE (SELECT ci.id, ci.new_item_code, item.upc_code
          FROM   consumer_items ci
                 JOIN item ON item.item_code = ci.item_code
          WHERE  ci.item_code IN ('a','b','c')
         ) v
  SET v.new_item_code = 'string' || v.upc_code
  

  EDIT: Added WHERE clauses

  jlrolin : Basically, my item.item_code = ('SET OF VALUES') that would reference item_codes in CONSUMER_ITEMS

  Tony Andrews : Do you mean like I have now added above?

  jlrolin : Oddly enough, it won;t let me modify a view, and the first statement brings about multiple values in a subquery errors

  Tony Andrews : So ITEM_CODE isn't unique in ITEMS table then? I had assumed it must be.

• Right, that looks great but the item.item_code = ci.item_code doesn't work because:

  SELECT distinct i.code, i.upc FROM item i, consumer_items ci WHERE i.ccode = '123132' AND i.scode = 'ACTIVE' AND i.upc IS NOT NULL AND ci.item_code = i.code AND i.code IN (SELECT DISTINCT tr.item_code FROM t_r tr WHERE tr.category = 'RRE') AND ci.NEW_ITEM_CODE IS NULL

  This is the distinct list of CODES and UPC associated with those codes that much be used to update the CONSUMER_ITEMS.

  new_item_code = (SELECT 'string' || item.upc_code FROM item WHERE item.item_code = (SELECT distinct i.code, i.upc FROM item i, consumer_items ci WHERE i.ccode = '123132' AND i.scode = 'ACTIVE' AND i.upc IS NOT NULL AND ci.item_code = i.code AND i.code IN (SELECT DISTINCT tr.item_code FROM t_r tr WHERE tr.category = 'RRE') AND ci.NEW_ITEM_CODE IS NULL));

  doesn't seem to work

  Tony Andrews : Sorry, but I didn't understand any of that! Perhaps we need to see some sample data?

  jlrolin : see above for better explanation.

• The list of i.ITEM_CODE, i.UPC is this:

  014940  070182132137
  018266  929245021085
  018268  729245021108
  018418  029245022815
  018419  129245022822
  018420  229245022839
  018421  529245022846
  018422  929245022853
  

  The first column is ITEM CODES, Second Column is UPCs. This is on the ITEMS table.

  The CONSUMER_ITEMS table essentially has a CONSUMER_ITEMS.ITEM_CODE as well. That's the LINK, but it also has a field called CONSUMER_ITEMS.NEW_ITEM_CODE. We want to fill the NEW_ITEM_CODE with the UPC from the corresponding ITEM_CODE in the list above with a concatentation of 'string' || UPC CODE FROM ABOVE.

  How we generate that list is:

  SELECT distinct i.code, i.upc
  FROM item i, consumer_items ci 
  WHERE i.ccode = '123434' 
  AND i.scode = 'ACTIVE' 
  AND i.upc IS NOT NULL 
  AND ci.item_code = i.code 
  AND i.code IN 
  (SELECT DISTINCT tr.item_code 
   FROM tr_table tr 
   WHERE tr.category = 'RRE') 
  AND ci.NEW_ITEM_CODE IS NULL
  

  This generates the ITEM_CODE, UPC list above. I need to update the CONSUMER_ITEMS that MATCH those codes above. Specifically, I need to update their NEW_ITEM_CODE fields, which are null, with the corresponding UPC concatenated with a STRING.

• Any thoughts?

• /*+ BYPASS_UJVC */

  use this hint if you are getting the following oracle error- ORA-01779: cannot modify a column which maps to a non key-preserved table