Editing data in SQL Server 2005

Posted on 2007-08-11
Medium Priority
Last Modified: 2010-03-19

This is a question about the use of  SQL server 2005 express.
I notice that when I right click on one of my tables (in the left hand pane), lets say 'users',  I am able to "open" it. Here I can see all of my data. If I want to edit one of the fields in this grid that is returned, I can simply select the field, backspace over the old data and enter in my new data. Then if I switch my cursor to another record, the edit I just made is saved.
However, if I do a query (select * from users). The result set I get back will not let me edit any of the fields in the same way I was able to before while just opening the whole table. Instead I need to actually edit the record by issuing an SQL statement.
Is there a way for me to edit the records returned by a SELECT query in the same way I'm able to edit them by opening the entire table.

FYI -- I can certainly write the UPDATE query but in the case of the data I'm dealing with it's much easier to edit the record the 'faster' way.

Thanks very much,
Question by:michaelshavel
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 1000 total points
ID: 19676442
you have to use the "open table", and then adjust the query that is used implicitely behind, by adding the sql view, and change and rerun the query.
LVL 22

Assisted Solution

dportas earned 1000 total points
ID: 19676623
You can edit a table using a view, provided the view and the table meet the requirements for updateable views. Do not attempt this in a production system. The table edit function is buggy and dangerous and should not be used with "live" data that can't be recovered.

Historical note: Microsoft dropped this feature from early betas of SQL Server 2005. It was put back (somewhat regretably in my opinion) only after some users complained. Judging by the results it has never received enough attention from the dev team or from testers.

Author Comment

ID: 19676643
That did it. Thanks.
Curious, why does it work this way and not when I run a "new query"?

Thanks again,

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question