The moose likes JDBC and Relational Databases and the fly likes Update the table rows through cursor Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Update the table rows through cursor" Watch "Update the table rows through cursor" New topic

Update the table rows through cursor

sudha swami
Ranch Hand

Joined: Apr 24, 2007
Posts: 186
I want to update the table rows by using cursors. Which one is the better way to use and why?
1)Select for Update
2)Where current Of
I am totally confused on this. Can you please help me out?

chris webster

Joined: Mar 01, 2009
Posts: 2288

SELECT ... FOR UPDATE locks the rows specified in the WHERE clause of your SELECT. The lock is released when you COMMIT or ROLLBACK the current transaction. This can be useful e.g. if you want to make sure nobody tries to update the same rows at the same time. But it can also be risky, because it is easy to lock a lot of records unnecessarily e.g. by selecting all the rows even if you only want to update one, or lock other people out by doing the SELECT and then forgetting to commit/rollback the transaction. So you should be careful how you use this.

UPDATE ... WHERE CURRENT OF [cursor] does exactly what it says: it updates the current record in your cursor. This works when your cursor is doing SELECT ... FOR UPDATE i.e. you have locked the record ready for updating it, which should stop anybody else updating it between the time you read it and the time you update it.

But I'm no Java expert, so you should get advice on how to manage this transaction and read consistency stuff within your Java application.

No more Blub for me, thank you, Vicar.
I agree. Here's the link:
subject: Update the table rows through cursor
It's not a secret anymore!