Archive for the ‘DELETE WHERE CURRENT OF’ tag
The FOR UPDATE and WHERE CURRENT OF statements
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 ROWID
s 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 …