9.05.702 - Progress Database
I created a Data Directive that updates a user define date field on the job operation table JobOper.Date03 with the first labor date for the operation and it works great.
What is the best way to run an update on all records in the table in one pass?
I would take the same (or similar) code that you wrote for your Data Directive and stick it in a BPM on say… ABCCode.GetNew
and instead of working on the ttJobOper table you’d do a FOR EACH on all of JobOper and make your chages there.
Run the Getnew on ABC Code and once your BPM is done, disable / delete it…
Another option is to use DMT to trigger each individual update… or an updatable BAQ…
Jose thanks that was the route I was looking at just wanted to make sure I wasn’t skipping something easier.
Patrick Winter
Patrick,
One thing I’d caution you on - and that’s how many records you have to update. I’ve run into issues doing this type of updates when the number of records is HUGE. I’ve found much better luck breaking it into smaller chunks. So, something like:
for each JobOper where JobOper.Company = ‘XXXXX’ and JobOper.Date03 = ?:
assign kntr = kntr + 1.
if kntr > 10000 then return.
do your update…
end.
I’m not sure what the issue was, I suspect it would have to do with rollback segments. I would DEFINITELY do this in a TEST environment first, start with a small number and see what your system will handle. It’s not a “one-pass” system, but it will allow you to run over time.
You’re also probably going to want to run this on an off-shift. That much database I-O is going to impact users.
Hope that helps.
Kevin Simon
SimsTrak Consulting, LLC