Solved

How to update a field in a table via SSMS ?

Posted on 2014-10-16
7
101 Views
Last Modified: 2014-11-22
I updated table in a db by right clicking and selecting Edit Top 200 Rows.

Then I realized I made a spelling error. missed a letter.
I did the same thing again, selecting Edit Top 200 Rows
In SQL Query pane the following shows:

SELECT     TOP (200) SITE_ID, SITE_NAME, TIMEZONE
FROM         Fishing_SITES

Then further down columns and rows

SITE_ID   SITE_NAME   TIMEZONE
1              FisingCO        NULL
2                                      NULL
NULL       NULL              NULL


I add the change the FisingCO to FishingCO in row 1 above and hit the execute exclamation Icon but it doesn't update.

How do I get this updated for the first 200 columns.  ( I know how to change 2000 to more from the Tools menu later if I need.)

Version info:  (2008)
Microsoft SQL Server Management Studio                                    10.0.1600.22 ((SQL_PreRelease).080709-1414 )
0
Comment
Question by:dee30
7 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 166 total points
ID: 40384369
don't use the exclamation mark unless you moved the cursor off to another row; that will run the update to the db;
then you can use the exclamation mark

the alternative is to use a UPDATE statment ... but ususally cumbersome for "GUI users"
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40384376
>hit the execute exclamation Icon but it doesn't update.
Once you enter the info on one cell , hit tab till you come to the next row; that will d it.

Between, it is not a preferred practice, you should use update statements
0
 

Author Comment

by:dee30
ID: 40384409
give the syntax steps for update statement use please.  thank you.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 167 total points
ID: 40384425
>I add the change the FisingCO to FishingCO in row 1
Execute this in SSMS...

UPDATE Fishing_SITES
SET SITE_NAME = 'FishingCO'
WHERE SITE_ID = 1

>How do I get this updated for the first 200 columns.
If the ID's are in order, use the below, but be VERY careful

UPDATE Fishing_SITES
SET SITE_NAME = 'FishingCO'
WHERE SITE_ID IN (SELECT TOP 200 SITE_ID FROM Fishing_SITES ORDER BY SITE_ID)
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 167 total points
ID: 40384639
Error says, the field is too small to hold that string. Seems like it is a varchar(8) field which can store only 8 characters. and you are trying to store 9 letters there. You need to ALTER the table

ALTER TABLE Fishing_SITES ALTER COLUMN SITE_NAME VARCHAR(12)
0
 

Author Comment

by:dee30
ID: 40384750
okay thanks will trying.  Thank you.
0
 

Author Closing Comment

by:dee30
ID: 40459805
Thank you.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

939 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

10 Experts available now in Live!

Get 1:1 Help Now