Troubleshooting
Problem
nzsql error, Update canceled: attempt to update a target row with values from multiple join rows
Cause
This UPDATE error occurs because there is a target row that is linked to more than one source row creating ambiguity which record to choose when applying UPDATE.
Diagnosing The Problem
This error is expected behavior; it is not a bug in the Netezza software.
Resolving The Problem
To resolve the error, duplicate records from the source table must be deleted and execute UPDATE statement must be re-applied.
The following examples illustrate how the error might occur when running the UPDATE against three different database engines. Note that UPDATE syntax is different in each one.
Netezza:
drop table test; create table test(k int, c int);
insert into test select 1, 1;
insert into test select 1, 1;
drop table target_test;
create table target_test(k int, c int);
insert into target_test select 1, 1;
update target_test a set c = b.c from test b where a.k = b.k; ERROR: Update canceled: attempt to update a target row with values from multiple join rows
The Netezza database throws the error and rolls back the update.
A solution to update problem above could be this:
create test_nodups as select k, max(c) as c from test group by k;
update target_test a set c = b.c from test_nodups b where a.k = b.k
Note that column test_nodups.k contains only unique values in the new table test_nodups. This resolves ambiguity what record has to be selected applying UPDATE.
Oracle:
drop table test
create table test(k int, c int)
insert into test select 1, 1 from dual
insert into test select 1, 1 from dual
drop table target_test
create table target_test(k int, c int)
insert into target_test select 1, 1 from dual
update target_test set c = (select test.c from test join target_test on test.k=target_test.k) Error: ORA-01427: single-row subquery returns more than one row
(State:S1000, Native Code: 593)
The Oracle database throws the error and rolls back the update.
MS SQL Server:
drop table test
create table test(k int, c int)
insert into test select 1, 1 from dual
insert into test select 1, 1 from dual
drop table target_test
create table target_test(k int, c int)
insert into target_test select 1, 1 from dual
update target_test set c = b.c from test b where target_test.k = b.k;
(1 row(s) affected)
The MS SQL Server database does not display an error; it performs the update.
Historical Number
NZ214008
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21569390