Home » SQL & PL/SQL » SQL & PL/SQL » How to update the rows? (Oracle 10g)
How to update the rows? [message #339675] Fri, 08 August 2008 02:13 Go to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi,
I have a table which has 29lacs records,now i need to update the rows for a particular column in that table.But i need to update the rows by splitting the total number of rows.for ex , first 5 lacs records and next 5 lacs.

i have used the simple update statement like:
update tablename
set serialnumber ='('||serialnumber||')'
where serialnumber is not null



But i need a condition again to update first 5 lacs and next 5 lacs seperately(Dateidentifier is not there in the table)
can i use rownum in where condition which i am not sure..


version of oracle:Oracle Database 10g Release 10.2.0.1.0 - 64bit Production
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


Thanks & Regards,
Hammer.
Re: How to update the rows? [message #339685 is a reply to message #339675] Fri, 08 August 2008 02:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do it in a single statement.

No, you can't use rownum.

What is a lac? Please use standard english and not local unit.

Regards
Michel
Re: How to update the rows? [message #339695 is a reply to message #339685] Fri, 08 August 2008 02:42 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi Michel,
Is there anyway to update the rows by splitting the total number of counts in the table.


Thanks and Regards,
Hammer.
Re: How to update the rows? [message #339698 is a reply to message #339695] Fri, 08 August 2008 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Split them by rowids for instance.

Regards
Michel
Re: How to update the rows? [message #339705 is a reply to message #339695] Fri, 08 August 2008 02:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why do you need to split the rows?
What is wrong with doing a single update - you've got less than 3 million rows - it's not a large amount of data.
Re: How to update the rows? [message #339712 is a reply to message #339705] Fri, 08 August 2008 03:21 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi JRowbottom,
Thanks for your reply.I used the single update statement.It says that totally twenty lakhs records are updated but when I see the table it is not updated.But same statement I used for a table which has seven lakhs records it is updated.
I can't able to point excatly where is the problem.

Thanks & Regards,
Hammer.
Re: How to update the rows? [message #339717 is a reply to message #339712] Fri, 08 August 2008 03:36 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You miss something.
Maybe you didn't set the value in update.
Maybe you didn't commit.
Maybe you didn't query the table you updated...

Anyway, update works the same way for X or Y number of rows.

Regards
Michel
Previous Topic: How to hide repeated details using SQL Query?
Next Topic: executing a function which returns boolean
Goto Forum:
  


Current Time: Fri Dec 09 13:33:36 CST 2016

Total time taken to generate the page: 0.10188 seconds