merge error (udb db2 v8.1.5 linux)

[Bob Stearns]

Does the following message get generated before or after the additional
conditions given in the merge are evaluated?

Error: The same row of target table "NULLID.AICA" was identified more
than once for an update, delete or insert operation of the MERGE statement.

The merge statement was:

// put the bhid into all of the records SHOULD BE 8
merge into nullid.AICA t1 using is3.animals t2
on t1.assoc=t2.assoc and t1.regnum=t2.regnum
when matched and
t1.bhid is null and
t1.prefix is null and
t2.prefix is null and
t1.regnum is not null and
t2.regnum is not null
then update set t1.bhid=t2.bhid
go

Is the message generated before all of the and conditions are evaluated
or after?

[Serge Rielau]

Bob Stearns wrote:

Does the following message get generated before or after the additional
conditions given in the merge are evaluated?

Error: The same row of target table "NULLID.AICA" was identified more
than once for an update, delete or insert operation of the MERGE statement.

The merge statement was:

// put the bhid into all of the records SHOULD BE 8
merge into nullid.AICA t1 using is3.animals t2
on t1.assoc=t2.assoc and t1.regnum=t2.regnum
when matched and
t1.bhid is null and
t1.prefix is null and
t2.prefix is null and
t1.regnum is not null and
t2.regnum is not null
then update set t1.bhid=t2.bhid
go

Is the message generated before all of the and conditions are evaluated
or after?

Yet another MERGE question.. hmmm.

The message is generated after the ON clause evaluates.
The extra conditions for WHEN MATCHED are e.g. to distinguish between
DELETE and UPDATE situtation.

So either you have to move some of those conditions into the ON clause
or you need to GROUP the source BY assoc, regnum - and aggregate.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab