Home » SQL & PL/SQL » SQL & PL/SQL » no data found
no data found [message #245044] Fri, 15 June 2007 01:19 Go to next message
ninja17
Messages: 12
Registered: June 2007
Junior Member
Hi, pls how can I exit this LOOP. I want to update some rows in a table and exit the cycle when all these rows are updated.

for example:

LOOP
update Table1 set CODE = '100'
where NAME like 'K%';

-- here I want to exit the loop when all rows are updated

END LOOP;

thanks for help, I am a beginner

Re: no data found [message #245053 is a reply to message #245044] Fri, 15 June 2007 01:31 Go to previous messageGo to next message
ninja17
Messages: 12
Registered: June 2007
Junior Member
and I dont want to use a cursor
Re: no data found [message #245057 is a reply to message #245044] Fri, 15 June 2007 01:33 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
you should just code

update Table1 set CODE = '100'
where NAME like 'K%';


and Oracle will handle the finding and updating the matching rows.

Re: no data found [message #245058 is a reply to message #245053] Fri, 15 June 2007 01:33 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
There's no need for a loop. Your update will update ALL records at once.

MHE
icon9.gif  Re: no data found [message #245062 is a reply to message #245058] Fri, 15 June 2007 01:38 Go to previous messageGo to next message
ninja17
Messages: 12
Registered: June 2007
Junior Member
thanks, but I need to use a LOOP because I want to commit after each 100 rows ;(
Re: no data found [message #245064 is a reply to message #245053] Fri, 15 June 2007 01:40 Go to previous messageGo to next message
ninja17
Messages: 12
Registered: June 2007
Junior Member


DECLARE
COMMIT_NUMBER NUMBER := 0;
BEGIN

LOOP
update Table1 set CODE = '100'
where NAME like 'K%';

IF MOD(COMMIT_NUMBER, 5000) = 0 THEN
COMMIT;
END IF;

-- here I want to exit the loop when all rows are updated

END LOOP;

END;
Re: no data found [message #245065 is a reply to message #245062] Fri, 15 June 2007 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is a stupid thing to commit every 100 rows.
Can you justify your need?

Regards
Michel
Re: no data found [message #245066 is a reply to message #245062] Fri, 15 June 2007 01:42 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
ninja17 wrote on Fri, 15 June 2007 08:38
thanks, but I need to use a LOOP because I want to commit after each 100 rows ;(

no, you don't want that. And your DBA doesn't want that either.

MHE
Re: no data found [message #245067 is a reply to message #245066] Fri, 15 June 2007 01:46 Go to previous messageGo to next message
ninja17
Messages: 12
Registered: June 2007
Junior Member
so after 10.000 rows .. that was an example .. but I cant find the way to exit the loop
Re: no data found [message #245069 is a reply to message #245067] Fri, 15 June 2007 01:56 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
ninja17 wrote on Fri, 15 June 2007 08:46
so after 10.000 rows .. that was an example .. but I cant find the way to exit the loop


No, you still don't want that. The update runs on all records, one time. There's nothing more performant than that. Do you really want to break the update into pieces and create a lot of log switches, context swithches, ...?

How large is the transaction, Ninja? What's the number of affected rows?

MHE
Re: no data found [message #245070 is a reply to message #245069] Fri, 15 June 2007 02:03 Go to previous messageGo to next message
ninja17
Messages: 12
Registered: June 2007
Junior Member
there are more than 1.000.000 updated rows, and I have 5 update statements in this procedure .. I really need to commit
Re: no data found [message #245072 is a reply to message #245044] Fri, 15 June 2007 02:09 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
by Maaher
There's no need for a loop. Your update will update ALL records at once.

Really, what is wrong with this approach? One update, no loop at all.
by ninja17
and I dont want to use a cursor

Another option is to bulk collect at least the primary key of Table1 into PL/SQL Collection and loop through it. However if you declined using cursor for performance issues, this approach will not be better.
I have to agree with Maaher
by Maaher
No, you still don't want that. The update runs on all records, one time. There's nothing more performant than that.
Re: no data found [message #245074 is a reply to message #245070] Fri, 15 June 2007 02:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I really need to commit

No you don't need it.

One question: what will you do if there is a (session/db/server) crash after the 128th commit?

Regards
Michel
Re: no data found [message #245075 is a reply to message #245072] Fri, 15 June 2007 02:16 Go to previous messageGo to next message
ninja17
Messages: 12
Registered: June 2007
Junior Member
yes, I know that update without loop would be the best, but without loop I dont know how to use this commiting.
Re: no data found [message #245077 is a reply to message #245075] Fri, 15 June 2007 02:17 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You don't need to do this "commiting":

ONE update
ONE commit

I still don't see the problem. Why do you insist on committing every N rows.

BTW: you still haven't answered my question.

MHE
Re: no data found [message #245079 is a reply to message #245074] Fri, 15 June 2007 02:18 Go to previous messageGo to next message
ninja17
Messages: 12
Registered: June 2007
Junior Member
One question: what will you do if there is a (session/db/server) crash after the 128th commit? //

yes I should create some logging
Re: no data found [message #245084 is a reply to message #245079] Fri, 15 June 2007 02:25 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
ninja17 wrote on Fri, 15 June 2007 09:18
One question: what will you do if there is a (session/db/server) crash after the 128th commit? //
Nothing. There is only ONE commit. The update either succeeded, or it failed.

MHE

[Updated on: Fri, 15 June 2007 02:26]

Report message to a moderator

Re: no data found [message #245107 is a reply to message #245084] Fri, 15 June 2007 03:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
That is exactly my point.

Regards
Michel
Re: no data found [message #245169 is a reply to message #245107] Fri, 15 June 2007 06:37 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The only reason I could see for breaking down the update into smaller parts and commit between them is if it's a table that is edited by other people at the same time, and you don't want to have one big update that locks all the updated rows for a long time so that nobody else can work during that.

In that case I would consider breaking down the update into smaller parts, like


update Table1 set CODE = '100'
where NAME like 'KA%';
commit;

update Table1 set CODE = '100'
where NAME like 'KB%';
commit;

update Table1 set CODE = '100'
where NAME like 'KC%';
commit;
......


for example.

But you didn't tell us WHY you need the commit, so we can't really help you to well.



Re: no data found [message #245188 is a reply to message #245169] Fri, 15 June 2007 07:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The only reason I could see for breaking down the update into smaller parts and commit between them is if it's a table that is edited by other people at the same time

Only true if there is no functional corelation between all the data.
Is the result of your report still valid if you already updated KA but not the others?

Regards
Michel
Re: no data found [message #245196 is a reply to message #245188] Fri, 15 June 2007 07:34 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

Is the result of your report still valid if you already updated KA but not the others?



Well, I could tell you that if it would me my report and my data, but ninja17 doesn't even tell us why he needs the commit, so I tried to weasel it out of him. Wink
Re: no data found [message #245282 is a reply to message #245044] Fri, 15 June 2007 13:59 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
A simple million row update is not a big deal, your update on K% will go quickly. If you commit in a loop, then you greatly increase the chance of getting the dreaded "snapshot to old" error. Updating in a cursor when there is no need has the following problems

1) It will take MUCH longer.
2) The chance of failure is MUCH higher
3) Your users will see data that is changed and unchanged during the query. Typically not a good idea.
4) And finally as a DBA and developer since Oracle 5 I can easily state that your plan is just plain bad programming. The only time I have had to do a cursor loop update like this is when I could not figure out how to do it in a single update and that has almost never happened.
Previous Topic: Advantages & Dis-advantages of Truncate & Delete...
Next Topic: quoted string not properly terminated...
Goto Forum:
  


Current Time: Wed Dec 07 10:38:44 CST 2016

Total time taken to generate the page: 0.13484 seconds