RE: Newbie SQL problem with UPDATE - SQL0811

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

In some cases, row in tableA is matched up with multiple rows in tableB. DB2
can't just pick one for you, you have to tell it what to do.
BEST way to correct this issue is to enhance your join so it guarantees
one-to-one match on the join.  This can be done by adding additional join
criteria (i.e. AND a.fieldB = b.fieldB).
However, you could HACK it by picking one of the joined rows by using an
aggregate function such as MAX,MIN,AVG, even SUM (although SUM rarely makes
sense).

BTW, for performance reasons, stick with the EXISTS and forget the COALESCE
in this case.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Re: Newbie SQL problem with UPDATE - SQL0811