×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
On 22 Jul 2013 06:31, Milyard, William A. wrote:
I know I've seen this in the archives but I can't find the solution.
I need to update one field where the entire row is a duplicate.
For example:
Pallet Date
915 2013-07-17
915 2013-07-19 <- Need to change to 2013-07-18
915 2013-07-19
918 2013-07-19
920 2013-07-19 <- Need to change to 2013-07-18
920 2013-07-19
UPDATE MYFILE B
SET B.DATE = '2013-07-18'
WHERE B.PALLET IN (
SELECT B.PALLET
From MYFILE B, MYFILE C
Where B.PALLET = C.PALLET
and RRN(B) > RRN(C)
AND B.DATE = '2013-07-19'
AND C.DATE = '2013-07-19')
<<SNIP>>
Seems unclear to me, what this has to do with RPG. Regardless...
At a minimum, the chosen correlation identifiers are somewhat
problematic. The WHERE clause apparently wants to correlate to the
outer update query, but because the same identifier is reused by the
inner query, there is presumably no correlation at all; i.e. all
references to the duplicate identifier, are scoped to the subquery.
Additionally, if a join were desirable, then using the JOIN syntax makes
that clear; although a join should not be necessary, because the
correlation would be the effective join. Having corrected just the
correlation identifiers might seem able to resolve the issue, however
there are still apparent issues. First, the RRN() is not allowed on the
outer file's correlation identifier within the inner query [on v5r3].
That could be solved by having the updated file be a VIEW of MYFILE with
the RRN() included as a named column for comparison in the subquery.
Second, the UPDATE does not explicitly limit the updates to only those
rows with the same specific\literal date referenced in the subquery;
i.e. both queries need to limit the selection to those rows.
UPDATE MYFILE B
SET B.DATE = '2013-07-18'
WHERE B.DATE = '2013-07-19' /* limit to only the 'wrong' date */
AND B.PALLET IN (
SELECT C.PALLET
From MYFILE C
Where C.PALLET = B.PALLET /* effects the join with next */
AND C.DATE = B.DATE /* avoid specifying literal again */
and RRN(B) < RRN(C) /* update lower of RRN values */
)
Assuming the restriction with RRN(B) remains in the release being
used, then precede the above request with the following CREATE, and
change the UPDATE to reference QTEMP/MYFILEVW instead of MYFILE, and
change the subquery to reference B.RN instead of RRN(B):
create view qtemp/myfileVW as
(select a.*, rrn(a) as rn
from myfile a) /* create update-capable VIEW with RRN() */
midrange.com
