Solved

How to update a field in a table via SSMS ?

Posted on 2014-10-16
7
109 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 143

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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

733 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