Question & Answer
Question
What does the ERROR: Update canceled: attempt to update a target row with values from multiple join rows mean?
Answer
When running a SQL UPDATE statement, you may receive the following error message:
- ERROR: Update canceled: attempt to update a target row with values from multiple join rows
This error is as a result of the update occurring to a single row with 2 or more values. For example, the error can be reproduced via:
- create table x (a int, b int, c varchar(1));
create table y (a int, b int, c varchar(1));
insert into x select 1, 1, 'A';
insert into y select 1, 1, 'C';
insert into y select 1, 1, 'B';
update x set c = y.c from y where x.a = y.a and x.b = y.b;
In Release 4.5.4 [4.6], the behavior of UPDATE queries that use a cross-join has changed. In previous releases, if such a query updated a table as a result of a self-join with no conditions, the query would complete; however, the query should have returned an error if the cross-join returned more than one row. An example query follows:
- update table2 set b = x.a from table2 y, table1 x where x.b = y.b;
As of Release 4.5.4 [4.6], this form of UPDATE query now returns the following error if the cross-join returns more than one row:
- ERROR: Update canceled: attempt to update a target row with values from multiple join rows.
If your query returns this error, you can rewrite the query to remove the target table from the FROM list, as follows:
- Update table2 set b = x.a from table1 x where x.b = table2.b;
[{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":null,"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Historical Number
NZ151641
Was this topic helpful?
Document Information
More support for:
IBM PureData System
Software version:
1.0.0
Document number:
467031
Modified date:
17 October 2019
UID
swg21575166