unable to update DB2 table

Question

Can you please help me in updating DB2 table and is there a better way to update this huge table? Adv thxs.

UPDATE RT.ITEM  IM SET 
IM.ITEMNAME = GT.ITEM_D, IM.ITEMSIZE = GT.SIZE, IM.COLOR = GT.COL,
IM.ITEMINFO = GT.ITEM_I WHERE IM.RET = 14 AND IM.LAN = 10 and
  IM.ITEMK IN ( SELECT GT.SN_N FROM GD.G_TEMP GT );  

Trying to update a table(RT.ITEM) from another schema table(GD.G_TEMP) and getting below error msg: [Code: -206, SQL State: 42703] DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=GT.ITEM_D

Answer 1

Your code won't work. DB2 doesn't support explicit JOIN in UPDATE. But you can do what you want with a correlated subquery:

UPDATE RT.ITEM IM
    SET (ITEMNAME, ITEMSIZE, COLOR, ITEMINFO) = 
         (SELECT GT.ITEM_D, GT.SIZE, GT.COL, GT.ITEM_I
          FROM GD.G_TEMP GT
          WHERE GT.SN_N = IM.ITEMK
          FETCH FIRST 1 ROW ONLY
         )
    WHERE IM.RET = 14 AND IM.LAN = 10 AND
          EXISTS (SELECT 1
                  FROM GD.G_TEMP GT
                  WHERE GT.SN_N = IM.ITEMK
                 );