DB2 - Problem description
Problem IT27705 | Status: Closed |
MERGE STATEMENT INCORRECTLY UPDATES TARGET ROW MULTIPLE TIMES. THE STATEMENT SHOULD HAVE RETURNED ERROR -788 | |
product: | |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 | |
Problem description: | |
Data corruption is possible with MERGE statement using DISTINCT. Statement should have returned SQL0788N. Example: create table S (C1 smallint, C2 varchar(10) ); create table T (C1 smallint, C2 varchar(10) ); insert into S values (1, 'A'); insert into S values (2, 'B'); insert into S values (3, 'C'); insert into S values (3, 'C2'); insert into S values (4, 'D'); insert into T values (1, 'A'); insert into T values (2, 'B'); insert into T values (3, 'C'); insert into T values (4, 'D'); db2 => merge into T using (select distinct c1, c2 from S) S on T.C1 = S.C1 when matched then update set T.C2 = T.C2 || '-' || S.C2 DB20000I The SQL command completed successfully. Both target rows with C1=3 are updated. The above statement should get error -788. db2 => select * from T; C1 C2 ------ ---------- 1 A-A 2 B-B 3 C-C2 4 D-D 4 record(s) selected. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * All * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * See Error Description, Please, install this fix. * **************************************************************** | |
Local Fix: | |
If the DISTINCT clause is not strictly necessary, remove it. Otherwise, ensure that each column in the source table-reference is also included in the MERGE search condition, e.g. for the example above, add predicate T.C2=S.C2: merge into T using (select distinct c1, c2 from S) S on T.C1 = S.C1 and T.C2 = S.C2 when matched then update set T.C2 = T.C2 || '-' || S.C2; | |
Solution | |
Workaround | |
not known / see Local fix | |
BUG-Tracking | |
forerunner : follow-up : IT27712 | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 09.01.2019 28.06.2020 28.06.2020 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |