MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘WHERE CURRENT IS’ tag

The FOR UPDATE and WHERE CURRENT OF statements

with 4 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