0
UPDATE UPI_ATTRIBUTE SET SITE_INC ='0' 
WHERE USER_PROFILING_NAME IN ('CAR_IMPLICIT','CAR_EXPLICIT')

Above is my query that I am using to update the columns in UPI_ATTRIBUTE table. And suppose if I need to rollback the above changes that I am doing with update query, then how can I use the delete query in the above case? Currently SITE_INC is empty. So after updating the table it will have 0 in that. And If I need to rollback the changes then I need to make it empty again.

How can I delete the value 0 from above column after updating.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
arsenal
  • 23,366
  • 85
  • 225
  • 331
  • 1
    Can you please describe the circumstances under which you'd need to roll this back? Is this an immediate thing if the update did not perform correctly, or is it something you'd need to do some time after the update? Are there other SQL statements that would be performed afterward that would determine if a rollback was necessary? – Jon Senchyna Jun 20 '12 at 02:07
  • Basically in future If I need to rollback the changes that I am doing with Update query above, then I need some sql to undo the changes, So Currently `SITE_INC` is empty and after updating it will be 0, so If I need to delete this 0 in future, then how can I do it? – arsenal Jun 20 '12 at 02:10
  • Are you using Microsoft SQL Server? – Jon Senchyna Jun 20 '12 at 02:10

2 Answers2

1

You cannot "delete" data to rollback an update.

Even if the only change that you'll do is set a flag from "NULL" to "Y" and vice versa, you can't really determine which value to update when you do multiple updates.

update 1 : set from "NULL" to "0"
update 2 : set from "0" to "something else"
update 3 : Rollback changes (rollback to which state)?

If you want to really rollback changes to specific rows (instead of the complete table, which can be done using Flashback), you can audit the changes to "remember" the values and then revert back to them.

Even then, you should consider which point to revert to, when you have multiple updates.

Rajesh Chamarthi
  • 18,568
  • 4
  • 40
  • 67
0

Not sure Oracle have final table/old table function for select/update.

For DB2 for z/OS, it have such function:

 SELECT LASTNAME, BONUS FROM FINAL TABLE
  (UPDATE EMP SET BONUS = BONUS * 1.3
          WHERE JOB = 'CLERK');

Then, the result set of the select include all the updated rows. you can use rownum or other unique value, then you can update it back to any value as you want.

Stevens Wu
  • 26
  • 2
  • Oracle has Flashback Query which is similar. http://docs.oracle.com/cd/B13789_01/appdev.101/b10795/adfns_fl.htm#1011142 – Jeffrey Kemp Jun 27 '12 at 04:40