Geek Answers Handbook

UPDATE using two tables, concatenation

I have two tables associated with this query that I need to create, and I'm not quite sure how to join these two tables for updating.

I have an ITEM table and CONSUMER_ITEMS. The ITEM table has a separate code for each element and a UPC code. I need to combine a string with ITEM.UPC_CODE in CONSUMER_ITEMS.NEW_ITEM_CODE, where CONSUMER_ITEMS.ITEM_CODE = (Concrete list ITEM.ITEM_CODES)

How can I update the CONSUMER_ITEMS.NEW_ITEM_CODE field?

It would essentially be equal to "string" || ITEM.UPC, but how can I refer to CONSUMER_ITEMS.ITEM_CODE equal to the specific ITEM_CODE in the ITEM_CODES list for updating.

+5
source share
4

, :

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');

, , , user_items , :

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: WHERE

+9

, , item.item_code = ci.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

CODES UPC, , 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));

,

0

i.ITEM_CODE, i.UPC:

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

- , - UPC. ITEMS.

CONSUMER_ITEMS CONSUMER_ITEMS.ITEM_CODE. LINK, CONSUMER_ITEMS.NEW_ITEM_CODE. NEW_ITEM_CODE UPC ITEM_CODE "string" || UPC CODE FROM ABOVE.

:

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 a list of ITEM_CODE, UPC above. I need to update CONSUMER_ITEMS that MATCHES these codes above. In particular, I need to update my NEW_ITEM_CODE fields, which are null, with the corresponding UPC combined with STRING.

0
source

/ * + BYPASS_UJVC * /

use this hint if you get the following oracle error: ORA-01779: cannot change column that maps to table not saved by key

-3
source

More articles:


All Articles