Using SQL Server Managment Studio SQL Icon to update records

Email This Page | Print This Page | Go Back


1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 3.00 out of 5)
Loading ... Loading ...

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.

Share Is Caring:
  • Digg
  • Facebook
  • Google
  • LinkedIn
  • MySpace
  • StumbleUpon
  • Technorati
  • TwitThis
Go Back


Rate This Article

2 Responses to “Using SQL Server Managment Studio SQL Icon to update records”

  1. Jason on May 4th, 2010 12:18 pm

    I’m really needing to find out how to make this work the way you have described.

    I can write the SQL query in SQL Server 2008 Management Studio – this selects the row(s) that I want, but this list of selected rows comes up in the “Results” tab where it won’t let me edit the results. I want to run the select query, and modify the fields exactly like you’ve said. The only way it seems I can modify the data in a table is to right click the table and select “Edit top 200 rows”. The only other way would be to run an update query …

    Please help – this is most annoying.

  2. admin on May 5th, 2010 3:28 am

    Jason,

    The key is make sure you have the “Query Designer” toolbar enabled. This should be the case by default. On that toolbar there is an icon shaped like a white square with the word “SQL” with the hint text of “Show SQL Pane”.

    I apologize if I’m repeating the instructions above, but the first step is to select whatever table you want to change the value in and choose “Edit Top 200 Rows”. Then select the “SQL” icon on the “View Designer” toolbar and this will typically display the “SQL Pane” at the top and the “Results Pane” at the bottom. Once you have the “SQL Pane” displayed then you can edit it like any other SQL and select the “!” icon on the “Query Designer” toolbar to execute the new SQL. The end result will be your modified SQL in the “SQL Pane” and the new table rows in the “Results Pane”. The rows in the “Results Pane” are editable and you can select whatever cell you want and edit it. To apply you can navigate to another row or select “X” to close the “SQL Pane”.

    I hope this helps. If you still cannot get it to work. Please let me know.
    Blake

Leave a Reply