im exporting procedures from sybase to netezza and in some of the procedures there is query that keeps giving me the "Update canceled: attempt to update a target row with values from multiple join rows" Data and table structure on both databases is the same, and this query woks on Sybase but not on Netezza
UPDATE table1 t1 SET t1.column1=t2.column1
FROM table2 t2
WHERE t1.column2=t2.column2;
What could cause this error if data and query is the same ?
This occurs because you have at least 1 row in table1 that joins too at least 2 rows in table 2, which causes confusion about which value should end up column1.
For example:
select * from table1;
COLUMN1 | COLUMN2
---------+---------
1 | 1
(1 row)
select * from table2;
COLUMN1 | COLUMN2
---------+---------
3 | 1
2 | 1
(2 rows)
UPDATE table1 t1 SET t1.column1=t2.column1
FROM table2 t2
WHERE t1.column2=t2.column2;
ERROR: Update canceled: attempt to update a target row with values from multiple join rows
It's not clear whether column1 in table1 should be set to 2 or 3, so the error is given and the transaction is rolled back.
I can't speak to why sybase allows this, however.
