Home » SQL & PL/SQL » SQL & PL/SQL » Update table with primary key in where clause (Oracle 10g)
Update table with primary key in where clause [message #572401] Tue, 11 December 2012 10:23 Go to next message
skm_fundo
Messages: 4
Registered: December 2012
Location: India
Junior Member
Is is required to check the number of rows updated in a table when the primary key of the table is used in the filter criteria of the update statement?
As I know,by default it will update only one record.
But if it happens to be an important transaction table and only one record is required to be updated, then is it the best practice to use the 'SQL%ROWCOUNT' check in the query, even if the update query is using primary key in filter clause.
Example:Consider Trans table with trans_id as primary key. Then:
Update Trans
set trans_status='pass'
where trans_id=123;
I know this will update only one record. But what is the best practice? Shall I use 'SQL%ROWCOUNT' after this update to double check whether the record is updated or not?

The question may appear silly, but is important for me.Please help...

Re: Update table with primary key in where clause [message #572402 is a reply to message #572401] Tue, 11 December 2012 10:35 Go to previous messageGo to next message
ThomasG
Messages: 3099
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, it is never required to check SQL%ROWCOUNT.

Can you come up with a good reason to check SQL%ROWCOUNT?

If the "transaction ids to be updated" are supplied by some process where it is possible that non-existent transaction ids are requested to be updated, then a check with SQL%ROWCOUNT to see whether 1 or 0 rows are updated might make sense.
Re: Update table with primary key in where clause [message #572403 is a reply to message #572401] Tue, 11 December 2012 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It can update 0 rows if there is no matching key in the table.

Regards
Michel
Re: Update table with primary key in where clause [message #572404 is a reply to message #572402] Tue, 11 December 2012 10:48 Go to previous messageGo to next message
skm_fundo
Messages: 4
Registered: December 2012
Location: India
Junior Member
Thanks for your quick help.
There is NO CHANCE of no-existant transaction ids. Because, the transaction ids are first created and inserted into the table. Then after the payment is made by a web service, the status is either updated to 'pass' or 'fail'.

I have been told to follow the best practice in oracle. Your answer supports my knowledge and information.
Thanks for that.
Still, is there any related documents/topics on this?

Thanks again for your help.
Re: Update table with primary key in where clause [message #572405 is a reply to message #572404] Tue, 11 December 2012 11:36 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
On what exactly?

Regards
Michel
Re: Update table with primary key in where clause [message #572406 is a reply to message #572405] Tue, 11 December 2012 11:51 Go to previous messageGo to next message
skm_fundo
Messages: 4
Registered: December 2012
Location: India
Junior Member
My customer is insisting to have 'SQL%ROWCOUNT' after the update query. He is questioning about, how to be sure that only one row is updated. I answered that, this is how oracle behaves and no need to have the check.

To that he again questioned and asked me to show the best practice on this or any topics/documents that shows that 'SQL%ROWCOUNT' is not required after the update queries where the primary key is used in the filter condition.

In case of not having any supporting document, I will have to implement 'SQL%ROWCOUNT' after the update query.
Re: Update table with primary key in where clause [message #572407 is a reply to message #572406] Tue, 11 December 2012 12:01 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no such document on such simple and obvious stuff.
There are so many things to discuss on the code.
Post one of your procedure and I bet I have tons of things to say.
This one does not matter.

Regards
Michel
Re: Update table with primary key in where clause [message #572408 is a reply to message #572407] Tue, 11 December 2012 12:16 Go to previous message
skm_fundo
Messages: 4
Registered: December 2012
Location: India
Junior Member
Thanks a lot for all your valuable inputs.

Regards
skm
Previous Topic: Get exact matching string
Next Topic: Question on savepint
Goto Forum:
  


Current Time: Sat Aug 23 13:08:27 CDT 2014

Total time taken to generate the page: 0.09175 seconds