Netezza UPDATE from one table to another

Question

This is my query that does not work in Netezza:

UPDATE TABLE1 A
SET A.COL1= (SELECT DISTINCT B.COL1 FROM TABLE2 B WHERE B.ID= A.ID AND B.DeptID=104)
WHERE A.DeptID=3

How do I re-write this query? Please help.

Solution

UPDATE TABLE1 A
SET A.COL1 = B.COL1
FROM TABLE2 B
WHERE
A.ID = B.ID AND 
A.DeptID = 3 AND 
B.DeptID = 104;

Comments

[ozzboy]

I get an error ^ found "INNER" (at char 57) expecting a keyword

[Donal]

sorry, forgot the from clause. I edited it - try it now.

[ozzboy]

Get another error error ^ found "B" (at char 157) expecting a keyword

[ozzboy]

Donal thanks for helping out. I was missing and AND crrected that but am getting another ERROR [42S02] ERROR: relation does not exist DB.USER.A

[Donal]

That syntax may not work with Netezza. I have changed it to use a different syntax

[ozzboy]

Sorry still no luck. ERROR: Update canceled: attempt to update a target row with values from multiple join rows

[Donal]

That means it is working. It just means that there are multiple rows in Table2 that match the criteria in the where clause for 1 row in Table1. I cannot see what your data is like, so I am not sure how to modify the where clause to return just 1 row from Table2 for each row in Table1.