SQL*Plus update loop.

[JoDev]

Please can some one help with some code here.

I have a table full of transfer numbers.
Each number has a status. (DONE, NOT DONE)

ie Trans_num || Status
1 DONE
2 NOT DONE

I want to update all the Status to done.
However i can not do it all in one go, as there are back ground rules that prevent the status being changed on a records if it does not meet the requirerments.

So if i attempt to update ALL staus in one go, , it will fail, and none of the records are changed.

Therefore i want a loop, so that 1 record at a time can be processed.
And errors are skipped over (or so i hope)

ie UPDATE mytable SET status = 'DONE' WHERE cdi_state = 'NOT DONE'

If there is anouther, anouther way way to update all of them in one go, and skip over errors that would be great.
Right now i have to ither update the records one by one of update them in small batches, which both are a pain becuase some times there can be up to 300 records to update)

Thanks

 

[Andrzejek]

I would find all "back ground rules that prevent the status being changed on a records if it does not meet the requirerments." and then do:

[tt]UPDATE mytable SET status = 'DONE' WHERE cdi_state = 'NOT DONE' AND all_criteria [/tt]



Have fun.

---- Andy

 

[JoDev]

Hi Andy,
Unforunatly an external company control the rules.
They are the middle people.

So i cant see the rules.
I can just change the status, and if i come across a transfer that it will not update, i have to manually research all our systems to find out why. Which is a long and painful process.

However like most systems, i have 100's of transfers numbers that i need to change stautus of, and if only one of those transfers fails , then none of them are updated, it wont tell me which one that caused the error, so thats when i have to update them one by one. Which is why i thought a loop might be better, and quicker.

Do you have any ideas? I hope every thing i said makes sence.

If you dont think a loop is the right way to go, is there a way where i can update a batch of numbers, and skip over the errors, so that the good ones can still get updated?

Thanks very much

 

[Andrzejek]

Did you solve your problem?

One other way may be: if you know your Trans_num's to update, you can do

[tt]UPDATE mytable SET status = 'DONE' WHERE cdi_state = 'NOT DONE' AND Trans_num IN (1, 3, 5, 7, ..., 125)[/tt]



Have fun.

---- Andy

 

[Thargy]

Jo,

it's essential that you get to the bottom of these meaningless rules, since they are preventing data cleansing from occurring. That means that the rules are enforcing bad data to remain in a system.

Also, a loop must be pointless. If you have the privileges to update one row, then you can update them all. If you can't get access to the data, provide a script to those who can, and get them to run it.

Regards

T