Search code examples
sqlnetezza

Netezza Update a Table Column by Joining to Another Table

I am getting an error by running an update, but I can not figure out where the issue is in Netezza. I appreciate some help.

ERROR [42S02] ERROR: relation does not exist DEVML_WORK.AGRINSHPUN.A

 update Table A
    set A.COL1 = B.COL2 
    from A left outer join  B 
    on A.CU_NUM=B.CU_NUM;
Solution

  • In general performance on correlated updates in Netezza is slow. Below are two examples that will get your query to work. The second in my experience speeds up large updates.

    -- Slow but works
 update  Table A
    set A.COL1 = B.COL2
    from B
    where A.CU_NUM=B.CU_NUM;

--Faster
--note "rowid" is a reserved word in netezza and references the internal id of the row, not a ddl id field
update A set col1 = sub.col2
from (select a.rowid as rown, b.COL2
from A a inner join
B b
on a.cu_num= b.cu_num) sub
where rowid = sub.rown;