Intellectual Mollusc

I just found a bug, sitting in my code for the last few years that has wiped clean years worth of categorisation data.

Update tblVersionInfo set Categories = 'New Categories'

opps. Notice what’s missing. Yes a where clause. This code was hidden away in a very rarely used routine that got run last week, overwriting all the category information in the entire table.

How many times have we all done this?

I would like to propose that the statement above in the next version of SQL should raise an error. Instead you should have to either supply a WHERE clause

 Update tblVersionInfo set Categories = 'New Categories' where VersionID = 55544

or specify you wish to update all

  Update   tblVersionInfo set Categories = 'New Categories' where ALL

as it stands the UPDATE statement is and _ has always been_ enormously destructive. With a DELETE statement you know you are doing something destructive, but with an update statement the destruction can happen by accident, not by design.

I feel its time to change the design.