how to update multiple rows with the same sequnce value

Question

I'm using DB2 and want to update several rows that meet my condition with the same next value from my sequence. Here is what I tried but this doesn't work as the next value is fetched for each row:-

update dependency dep set vid=NEXT VALUE FOR seq_VID where id in ('8371','8372','8373')

id is the the primary key and seq_VID is a sequence. So what I had hoped was that say the next sequence value was 99, that 99 would be set for all 3 rows (and not 99,100,101 as is the case with this). My workaround is to break it into separate statements for each id in my list, i.e.

update dependency dep set vid=NEXT VALUE FOR seq_VID where id= ('8371')
update dependency dep set vid=PREVIOUS VALUE FOR seq_VID where id= ('8372')
update dependency dep set vid=PREVIOUS VALUE FOR seq_VID where id= ('8373')

But I'd like to execute this in one SQL statement if possible - any ideas?

Answer 1

If you always knew that you wanted to put the 'previous' sequence value on the two rows after the one you updated with the 'next' next value you might be able to use a compound trigger, similar to the following (Oracle syntax, please forgive):

CREATE OR REPLACE TRIGGER DEPENDENCY_COMPOUND
  FOR UPDATE ON DEPENDENCY
  COMPOUND TRIGGER

  TYPE tDependency_row_table IS TABLE OF DEPENDENCY%ROWTYPE;
  tblDependency_rows  tDependency_row_table := tDependency_row_table();

  AFTER EACH ROW IS
  BEGIN
    tblDependency_rows.EXTEND;
    tblDependency_rows(tblDependency_rows.LAST).ID = NEW.ID+1;
    tblDependency_rows(tblDependency_rows.LAST).VID = seq_VID.CURRVAL;

    tblDependency_rows.EXTEND;
    tblDependency_rows(tblDependency_rows.LAST).ID = NEW.ID+2;
    tblDependency_rows(tblDependency_rows.LAST).VID = seq_VID.CURRVAL;
  END;

  AFTER STATEMENT IS
  BEGIN
    FOR i IN tblDependency_rows.FIRST..tblDependency_rows.LAST LOOP
      UPDATE DEPENDENCY
        SET VID = tblDependency_rows(i).VID
        WHERE ID = tblDependency_rows(i).ID;
    END LOOP;
  END;
END DEPENDENCY_AU;

Then you'd issue your update statement as

UPDATE DEPENDENCY
  SET VID = seq_VID.NEXTVAL
  WHERE ID = 8371;

and the trigger should take care of updating the other two rows.

The compound trigger is useful in Oracle 11+ to help work around the 'mutating table' error, which occurs when a trigger attempts to SELECT, INSERT, UPDATE, or DELETE data in the same table which the trigger is on.

This is a rather contrived situation and makes some huge assumptions about which rows should be updated, but perhaps it will prove useful.

Share and enjoy.