Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

The FOR UPDATE and WHERE CURRENT OF statements

with 3 comments


The FOR UPDATE clause has been part of Oracle SQL for years. As part of SQLJ, they introduced the WHERE CURRENT OF clause in Oracle 10g. The WHERE CURRENT OF clause allows you to join on ROWIDs the cursor and an UPDATE or DELETE statement in a loop.

For example, you can do something like this:

Alternatively, you can wrap this in a bulk operation (that doesn’t do anything really because it’s the same table), like this:

I have to ask why you don’t simply write a correlated UPDATE or DELETE statement, like this:

There hasn’t been time to run any tuning diagnostics on this but perhaps it should go into the queue of what-ifs. Any thoughts are welcome …

Advertisements

Written by maclochlainn

August 24, 2008 at 5:47 pm

3 Responses

Subscribe to comments with RSS.

  1. “where current of” successfully works on Oracle 9:
    Oracle9i Enterprise Edition Release 9.2.0.1.0 – 64bit Production

    Gokhan Tuna

    August 25, 2008 at 10:24 am

  2. That’s great to hear it does work. I went back and checked that documentation, and I’d read it wrong. It says, “Oracle does not currently support positioned UPDATE or positioned DELETE by way of a WHERE CURRENT OF clause, as specified by the SQLJ specification. Instead, Oracle recommends the use of ROWIDs to simulate this functionality.”

    maclochlainn

    August 25, 2008 at 3:10 pm

  3. The correlated UPDATE and DELETE is by far a more elegant solution so long as there aren’t any performance issues. The more code I write the more I appreciate elegance over obscurity and terseness. Nice post!

    Mike Farmer

    August 28, 2008 at 9:35 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: