Re: Help with SQL Update using join files

Your subselect is expected to return a scalar value (which would be the
value of ifqcm for one one row), but the error is telling you multiple rows
are being returned.

(select ifqcm from vinitmb join vinbini

on bacmp = ifcomp and baloc = ifloc and baitem = ifitem
where ifqcm <> baqcm and babloc = 'WHS')

Given what you've stated, there can be multiple bins for each
company/location/item unless there is some rule you haven't mentioned that
enforces a single bin per item at each location.

Where file vinitmb is unique at the company/location/item level and vinbini

is unique at the company/location/item/bin level.

You could use grouping functions ( sum( ifqcm )- or max( ifqcm ) ) to
ensure a single value is returned.

UPDATE vinbini
SET baqcm = ( SELECT SUM( ifqcm ) FROM vinitmb JOIN vinbini ON bacmp=ifcomp
AND baloc=ifloc AND baitem=ifitem WHERE SUM( ifqcm)<>baqcm AND babloc='WHS'
GROUP BY bacmp,baloc,baitem )
WHERE EXISTS ( SELECT 1 FROM vinitmb JOIN vinbini ON bacmp=ifcomp AND
baloc=ifloc AND baitem = ifitem WHERE SUM( ifqcm )<>baqcm AND babloc='WHS'
GROUP BY bacmp,baloc, baitem )
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.