OraQA

The following SQL pattern can be used to rollback committed data based on the group ID in a SQL.
(The restriction of this pattern is that the primary key (id) should not be modified)

create table flash_tab as
select 10 groupId, 1 id , 'A' name from dual
union all
select 10 groupId, 2 id , 'B' name from dual
union all
select 10 groupId, 3 id , 'C' name from dual
union all
select 10 groupId, 4 id , 'D' name from dual
union all
select 20 groupId, 5 id , 'E' name from dual
union all
select 20 groupId, 6 id , 'F' name from dual
union all
select 20 groupId, 7 id , 'group_20_testing_row_data' name from dual;

ALTER TABLE flash_tab ADD (CONSTRAINT pk_id PRIMARY KEY (id));

select * from flash_tab order by groupId, id;

   GROUPID         ID NAME
---------- ---------- -------------------------
        10          1 A
        10          2 B
        10          3 C
        10          4 D ---**All group 10 data will be rollback later.
        20          5 E
        20          6 F
        20          7 group_20_testing_row_data

7 rows selected.
update flash_tab set name = 'group_name_changed' where id = 1;
delete flash_tab where id = 2;
update flash_tab set groupId= 30,name ='group_num_changed'where id = 3;
update flash_tab set groupId= 40, name ='group_num_name_changed' where id = 4;
insert into flash_tab(groupId, id, name ) values (10, 99, 'group_10_newRow');
delete flash_tab where id = 5;
update flash_tab set name = 'No_RollBack_group_20' where id = 6;
insert into flash_tab ( groupId, id, name ) values (20, 28, 'group_20_newRow');

SQL> commit;

select * from flash_tab order by groupId;

   GROUPID         ID NAME
---------- ---------- -------------------------
        10          1 group_name_changed
        10         99 group_10_newRow
        20          6 No_RollBack_group_20
        20          7 group_20_testing_row_data
        20         28 group_20_newRow
        30          3 group_num_changed
        40          4 group_num_name_changed

7 rows selected.
variable groupId_input  number
exec :groupId_input  := 10;

—————————-SQL Solution—————–

MERGE INTO flash_tab tgt
USING (SELECT src.groupId, src.id, src.name,
              tgt.groupId AS tgt_groupId,
              tgt.id AS tgt_id, tgt.name AS tgt_name
        FROM flash_tab AS OF
             TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' SECOND) src
        FULL OUTER JOIN flash_tab tgt ON (src.id = tgt.id)
      ) src
ON (src.tgt_id = tgt.id)
WHEN MATCHED THEN
UPDATE
SET tgt.groupId= src.groupId, tgt.name = src.name
WHERE (LNNVL(tgt.groupId = src.groupId) OR LNNVL(tgt.name = src.name))
  AND (NVL(src.groupId, tgt.groupId) = :groupId_input )
DELETE
WHERE LNNVL(src.id = tgt.id)
  AND tgt.groupId IS NULL AND tgt.name IS NULL
WHEN NOT MATCHED THEN
  INSERT (tgt.groupId, tgt.id, tgt.name)
  VALUES (src.groupId, src.id, src.name)
WHERE src.groupId= :groupId_input;

SQL> commit;

select * from flash_tab order by groupId, id;

  GROUPID         ID NAME
---------- ---------- -------------------------
        10          1 A
        10          2 B
        10          3 C
        10          4 D ---** Roll back only the group 10 data **------
        20          6 No_RollBack_group_20
        20          7 group_20_testing_row_data
        20         28 group_20_newRow

Filed under Administration, SQL. Follow any responses to this entry through this RSS 2.0 feed.