Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Confirm before delete/update in SQL Management Studio?

So for the second day in a row, someone has wiped out an entire table of data as opposed to the one row they were trying to delete because they didn't have the qualified where clause.

I've been all up and down the mgmt studio options, but can't find a confirm option. I know other tools for other databases have it.

like image 542
Greg J Avatar asked Nov 11 '08 16:11

Greg J


People also ask

When you update a table what is best practice to do prior to deleting the data?

One thing that you should do before performing mass updates or deletes is to run a select statement using conditions provided.

Can we undo update command in SQL?

In the Object Explorer, right-click the object, folder, or database with changes you want to undo, select Other SQL Source Control tasks > Undo changes. Alternatively, right-click an object on the Commit tab, and click Undo changes.

How do I check for SQL updates?

Checking for updates (manually)Open SSMS, click on tools. Select the check for updates. A new window will pop up, displaying the current version of SQL Server Management Studio components and the latest version available.


3 Answers

I'd suggest that you should always write SELECT statement with WHERE clause first and execute it to actually see what rows will your DELETE command delete. Then just execute DELETE with the same WHERE clause. The same applies for UPDATEs.

like image 75
Rockcoder Avatar answered Sep 21 '22 13:09

Rockcoder


Under Tools>Options>Query Execution>SQL Server>ANSI, you can enable the Implicit Transactions option which means that you don't need to explicitly include the Begin Transaction command.

The obvious downside of this is that you might forget to add a Commit (or Rollback) at the end, or worse still, your colleagues will add Commit at the end of every script by default.

You can lead the horse to water...

You might suggest that they always take an ad-hoc backup before they do anything (depending on the size of your DB) just in case.

like image 39
CJM Avatar answered Sep 17 '22 13:09

CJM


Try using a BEGIN TRANSACTION before you run your DELETE statement.

Then you can choose to COMMIT or ROLLBACK same.

like image 36
Galwegian Avatar answered Sep 21 '22 13:09

Galwegian