Is there a way to manually change/update an ID number in SQL Server Management Studio

The ID number are auto-incremental and some rows were deleted so there are numbers in between where there are a couple of instances were I would like to change one or two of those ID numbers for continuity but is grayed out. Is there a way to do this with the GUI interface?

For example I have rows

1
4
5
6
7

I would like to change the 4 to a 2 and the 5 to a 3.
ID is the primary key and is set to "identity".

Thank you in advance.
colonelblueAsked:
Who is Participating?
 
Bhavesh ShahConnect With a Mentor Lead AnalysistCommented:
Hi,

You can do that by

DBCC CHECKIDENT('YourTableName', RESEED, 1)
0
 
sammySeltzerConnect With a Mentor Commented:
use identity reseed

DBCC CHECKIDENT (yourtable, reseed, startnumber)


0
 
Bhavesh ShahConnect With a Mentor Lead AnalysistCommented:
Hi,

As you said its PK so if other table relates to this table then you need to check integrity of FK.

either you need to update id of FK table.

or need to change cascade relations.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
colonelblueAuthor Commented:
Thank you both. If you don't mind me asking since I am more reliant on using the GUI ( For example using the diagrams, dropdown boxes, clicks ) because I am a newb and wouldn't even know where to run that code for a single row, is there a way to do it using the interface?
0
 
Bhavesh ShahConnect With a Mentor Lead AnalysistCommented:
Hi.

Just paste one of the code in query window.

Give table name where i write ur tablename.

Select database from database list which u want to use.

If you wanted to do via GUI then open ur table in design mode,first set auto identity to No for id column.

2nd save the table

3rd again set yes to auto identity column.

I knw u r new to sql but try 2 do,if u stuck,we are here to help u.

I suggest go with 1st option.
0
 
colonelblueAuthor Commented:
Thank you especially Brichsoft.
You guys are awesome.
0
 
dportasCommented:
Only use the GUI in this way if you have a backup or don't mind losing the data. Also make sure there is no-one else using the database. Using the GUI to modify tables like this can sometimes render the whole database ususable.

I strongly recommend you learn to use T-SQL so that you can write and execute SQL statements properly - that way you will have much more control over what you are doing.

IDENTITY columns have the irritating "feature" that they don't permit updates on the column. The easiest way to get around this may be to delete the rows and reinsert them.

The new SQL Server 2011 supports sequences, which makes it easier to create a surrogate key that you can update.
0
 
dportasCommented:
Correction: "ususable" = "unusable"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.