?
Solved

How to update a field in a table via SSMS ?

Posted on 2014-10-16
7
Medium Priority
?
120 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 498 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 501 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 501 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

777 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