Update table with a join

Question

I am trying to update data from 1 table into another, however I don't know the structure required.

UPDATE ITEM_WMS iw 
JOIN ITEM_CBO ic ON iw.ITEM_ID = ic.ITEM_ID
SET iw.critcl_dim_1 = ic.unit_length,
    iw.critcl_dim_2 = ic.unit_height,
    iw.critcl_dim_3 = ic.unit_width
WHERE ic.COLOUR_DESC = 'B4F';

Answer 1

That's wrong syntax for Oracle. MERGE might be a simpler (better?) option:

MERGE INTO item_wms iw
     USING item_cbo ic
        ON (ic.item_id = iw.item_id)
WHEN MATCHED
THEN
   UPDATE SET
      iw.critcl_dim_1 = ic.unit_length,
      iw.critcl_dim_2 = ic.unit_height,
      iw.critcl_dim_3 = ic.unit_width
           WHERE ic.colour_desc = 'B4F';

---

If it has to be UPDATE, then:

UPDATE items_wms iw
   SET (iw.critcl_dim_1, critcl_dim_2, critcl_dim_3) =
          (SELECT ic.unit_length, ic.unit_height, ic.unit_width
             FROM item_cbo ic
            WHERE     ic.item_id = iw.item_id
                  AND ic.colour_desc = 'B4F')
 WHERE EXISTS
          (SELECT NULL
             FROM item_cbo ic1
            WHERE     ic1.item_id = iw.item_id
                  AND ic1.colour_desc = 'B4F');

(exists part is here to skip rows that shouldn't be updated; otherwise, you'd set those columns to null)