Using SQL Server Managment Studio SQL Icon to update records

While working with SQL Server, I Often find myself needing to just change 1 value in a database record on a production server.  Usually, this happens after working with SQL Query Analzyer to research a problem and then realize that I can’t select the resulting rows below to fix the wrong value.  So how do you do it quickly?  There are a few ways to do this.

One wat would be to open up SQL Query Analzyer in SQL Server and write an “UPDATE” statement but this always worries me because an “UPDATE” statement can be very dangerous if not properly formed and when you are in a hurry.  Imagine updating a password for a database of 300,000 users and forgetting to put the “WHERE” clause and whoops, you just reset everyone’s password.  This actually happened to me once when I was in a hurry and I was lucky that I had executed the UPDATE statement on a copy of the production database before trying it on the real one.

My preferred method when doing a quick one-time change is to use SQL Server Management Studio for SQL Server 2005 or 2008 and “right-click” my mouse on the particular table and choose “open”.  Depending on the size of the recordset, I might stop the listing or let it continue.  I then select the “SQL” icon in the toolbar in the upper menu to open the database table in SQL mode to allow me to write SQL statements to limit the resulting recordset.  By default the SQL displayed in the SQL window is the standard “SELECT” with all rows.  I then add my “WHERE” clause such as “WHERE username=’gotchanow’ if wanting to update the password for that particular user.  Once it displays the row or rows based on the updated SQL statement, I then select the field to change, type in the new value and then apply the change.

So this is my recommended way of making a safe and simple change to a row or rows in a database without risking a mistake with an “UPDATE” statement.  I’m sure there are other ways using SQL Server 2005 or SQL Server 2008 Management Studio, and I would welcome hearing from you so we can share your suggestions with others.