Re: Help with SQL Update using join files

On 22 Aug 2012 07:12, Jeff Young wrote:

I have the following SQL statement:

update vinbini
set baqcm =
( select ifqcm
from vinitmb join vinbini
on bacmp = ifcomp and baloc = ifloc and baitem = ifitem
where ifqcm<>baqcm and babloc = 'WHS' )
where exists
( select 1
from vinitmb join vinbini
on bacmp = ifcomp and baloc = ifloc and baitem = ifitem
where ifqcm<>baqcm and babloc = 'WHS' )

Where file vinitmb is unique at the company/location/item level and
vinbini is unique at the company/location/item/bin level.
I want to update a field in vinbini for the 'WHS' bin records only
when the committed qty in vinitmb does not equal the value in
vinbini.
I am receiving the Result of SELECT more than one row message.
What am I missing?

Rather than using the explicit JOIN, ask for the matching record [using correlation identifiers] from the joined-to file, to [i.e. matching] the row being updated... for which the implementation of the query likely would be that same join. While I do not understand the request itself for not fully understanding the data\relationships [would the quantity from the bin file need to be a summation?], so possibly the following is still incorrect, how that _effective_ join is implied by the revised UPDATE request should be conspicuous:

update vinbini U
set U.baqcm =
( select ifqcm
from vinitmb
where U.bacmp = ifcomp and U.baloc = ifloc and U.baitem = ifitem
and ifqcm<>U.baqcm
)
where exists
( select 1
from vinitmb
where U.bacmp = ifcomp and U.baloc = ifloc and U.baitem = ifitem
and ifqcm<>U.baqcm
)
and U.babloc = 'WHS'