× 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.



Curiosity SQL question.

I have file which has a unique key on FLDA and FLDB. I want to update FLDB to a constant value for any one, but only one, of each set of FLDA values. (It’s setting up a test environment.)

FLDA FLDB
AAA XXX
AAA YYY
AAA ZZZ
BBB QQQ
BBB RRR
CCC TTT

When updated it should be:
FLDA FLDB
AAA NEW***
AAA YYY
AAA ZZZ
BBB NEW***
BBB RRR
CCC NEW***

I thought I could easily do it with a self join, like this: (Executed with RUNSQLSTM, on V6R1, current on PTFs)

update MYLIB/MYFILE a
set FLDB = 'NEW***'
where not exists
(select 1
from MYLIB/MYFILE b
where a.FLDA = B.FLBD
and b.FLDB = 'NEW***'
)
Doesn’t work—fails with a duplicate key.

The sub-select seems to be materialized once, where I thought it would be evaluated for every row from the update statement.

If I tell RUNSQLSTM that it can’t copy data, then it doesn’t even start.

Maybe I'm losing it earlier in the week than normal.

(I solved the problem with a short RPG program.)

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.