Archive for the ‘UPDATE 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 of 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 …
Subscribe via RSS