ORACLE: UPDATE using two tables, Concatenation

Question

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.

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

Any thoughts?

Answer 5

/*+ 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