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.

Greenroom

What error message are you getting ? – Faxun

Netezza doesn't support subqueries like this...use join syntax instead. – Khat

Answer 1

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

Princess

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

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

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

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 – Greenroom

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

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

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. – Princess