MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

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:

UPDATE   item i1
SET      last_updated_by = 3
,        last_update_date = TRUNC(SYSDATE)
WHERE    EXISTS (SELECT NULL
                 FROM    item i2
                 WHERE   i2.item_id BETWEEN 1031 AND 1040
                 AND     i1.ROWID = i2.ROWID);

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 …

Written by maclochlainn

August 24th, 2008 at 5:47 pm

3 Responses to 'The FOR UPDATE and WHERE CURRENT OF statements'

Subscribe to comments with RSS or TrackBack to 'The FOR UPDATE and WHERE CURRENT OF statements'.

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

    Gokhan Tuna

    25 Aug 08 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

    25 Aug 08 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

    28 Aug 08 at 9:35 pm

Leave a Reply