重新插入一列中的值 [英] Re inserting the values in one column

问题描述

我的表结构如下。

 sl.no name city 
  1  ab 
  2  cd 
  3  ef 
  4  gh 
 

我有上表。说我有100个这样的数据。现在以防万一我们考虑删除了第3行数据。所以sl不会像

 sl.no 
  1  
  2  
  4  

现在我想做什么。我希望第3行sl不应该是3，无论我在表中做出什么改变。我怎么能做到这一点？

解决方案

这是一个坏主意：如果你正在为我的身份使用身份字段！然后可能有其他字段非正式使用它作为外键，因此SQL不会重复使用或重新分配数字。



相反，返回rownumber

 SELECT ROW_NUMBER（）OVER（ORDER BY id）为RowNum，[name]，city FROM MyTable 

它不会因为有两种可能性是有意义的。



1）如果它的身份领域比完整性更重要。



2）如果它不是标识字段，则必须更新所有值。这不是个好主意。



我还不清楚你为什么要这样做。



问候..：）

I have the table structure as follows.

sl.no     name        city
1          a           b
2          c           d
3          e           f
4          g           h

I have the above table. say i have totally 100 datas like that. Now in case lets consider i have deleted the 3rd row data. So the sl no will be like

sl.no
1
2
4

Now what i want to do. I want the 3rd row sl no should be 3 irrespective of whatever the changes i make in the table. How can i accomplish this?

解决方案

This is a bad idea: if you are using an Identity field for your I'd! Then there could be other fields using this informally as a foreign key, so SQL does not reuse or reassign numbers.

Instead, return the rownumber

SELECT ROW_NUMBER() OVER (ORDER BY id) as RowNum, [name], city FROM MyTable

It won't makes sense as there are two possibilities.

1) If its an identity field than integrity matters.

2) If its not an identity field,than you have to update all the values. That is not a good idea.

I am still not clear why you want such approach.

Regards..:)

这篇关于重新插入一列中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！