SQL Server table - Update Order by

I have a SQL Server table with fields: id, city, country. I imported this table from Excel file, everything is imported successfully, but id field is not ordered by number. The tool I use imp开发者_JAVA百科orted the rows in some random number.

What kind of Update command I should use from SQL Server Management Studio Express to re-order my ids?

Do you have a primary key and a clustered index on your table? If not, id is a good candidate for a primary key and when you create that the primary key it will be the clustered index.

Assuming this is your table

create table CityCountry(id int, city varchar(10), country varchar(10))

And you add data like this.

insert into CityCountry values (2, '2', '')
insert into CityCountry values (1, '1', '')
insert into CityCountry values (4, '4', '')
insert into CityCountry values (3, '3', '')

The output of select * from CityCountry will be

id          city       country
----------- ---------- ----------
2           2          
1           1          
4           4          
3           3  

A column that is primary key can not accept null values so first you have to do

alter table CityCountry alter column id int not null

Then you can add the primary key

alter table CityCountry add primary key (id)

When you do select * from CityCountry now you get

id          city       country
----------- ---------- ----------
1           1          
2           2          
3           3          
4           4      

Just use the order by part of the select statement to order them.

If I understood you correctly, you want all the ids to have consecutive numbers 1,2,3,4...

Image your table contents is:

select *
  from yourTable

id          city       country
----------- ---------- ----------
1           Madrid     Spain
3           Lisbon     Portugal
7           Moscow     Russia
10          Brasilia   Brazil

(4 row(s) affected)

To reorder the ids, just run this:

declare @counter int = 0

update yourTable
   set @counter = id = @counter + 1

(4 row(s) affected)

You can now check, that indeed all the ids are reordered:

select *
  from yourTable

id          city       country
----------- ---------- ----------
1           Madrid     Spain
2           Lisbon     Portugal
3           Moscow     Russia
4           Brasilia   Brazil

(4 row(s) affected)

However, you need to be careful with this. If some table has a Foreign key to this id column, then you need first to disable that FK, update this table, update the values in other tables that have FK's pointing to yourTable finally enable again the FKs

First, I think you may have some misconceptions about the purpose of the Id column. The Id column is probably a surrogate key; i.e. an arbitrary value that is unique and non-null that is never shown to the user. Thus, it should not be implied to have any inherit meaning or sequence. In fact, you should always have another column or columns that are marked as being unique to represent a "business key" or a set of values that are unique to the user. In your case, city, country should probably be unique (although you will likely need to add province or state as it is common to have the same city exist in the same country multiple times.)

Now, that said, it is possible to re-sequence your Ids if the following are true:

1. The Id column is not an identity column. Since this was from an import, I'm going to guess this is true.
2. There does not exist a relationship to the table where Cascade Update is not enabled.
3. You are using SQL Express 2005 or later:

Update MyTable
Set Id = T2.NewId
From    (
        Select Id
            , Row_Number() Over ( Order By Id ) As NewId
        From MyTable
        ) As T1
    Join MyTable As T2
        On T2.Id = T1.Id