CODEWITHSUNDEEP
sqlnetezza
Mehran F Langerudi
Mehran F Langerudi

Reputation: 130

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;

Upvotes: 3

Views: 10750

Answers (1)

Niederee
Niederee

Reputation: 4295

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;

Upvotes: 6

Related Questions