Editing data in SQL Server 2005

Posted on 2007-08-11
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 142

    Accepted Solution

    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

    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.
    LVL 1

    Author Comment

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

    Thanks again,

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    794 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now