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