sql - 根据行中的值,用值更新 SQL 行的最快方法是什么?
问题描述
table
我有以下SQL Server
调用tblProducts
:
+-----------+--------------+-------------+
| pkProduct | fkProductID | intIssue |
+-----------+--------------+-------------+
| 1 | 10 | 1 |
| 2 | 10 | 2 |
| 3 | 10 | 4 |
| 4 | 11 | 1 |
| 5 | 11 | 2 |
| 6 | 11 | 3 |
| 7 | 11 | 5 |
| 8 | 12 | 1 |
| 9 | 13 | 1 |
| 10 | 13 | 4 |
| 11 | 14 | 1 |
| 12 | 14 | 3 |
| 13 | 14 | 6 |
| 14 | 15 | 1 |
| 15 | 16 | 1 |
+-----------+--------------+-------------+
随着时间的推移,无论出于何种原因,行已被删除,现在问题编号存在差距。我希望问题编号按顺序运行,table
如下所示:
+-----------+--------------+-------------+
| pkProduct | fkProductID | intIssue |
+-----------+--------------+-------------+
| 1 | 10 | 1 |
| 2 | 10 | 2 |
| 3 | 10 | 3 |
| 4 | 11 | 1 |
| 5 | 11 | 2 |
| 6 | 11 | 3 |
| 7 | 11 | 4 |
| 8 | 12 | 1 |
| 9 | 13 | 1 |
| 10 | 13 | 2 |
| 11 | 14 | 1 |
| 12 | 14 | 2 |
| 13 | 14 | 3 |
| 14 | 15 | 1 |
| 15 | 16 | 1 |
+-----------+--------------+-------------+
目前我已经添加table
到我的DataSet
并在vb.net
代码中使用它来更新行:
Dim currentProductsTable As tblProductsDataTable
Using taProduct As New tblProductsTableAdapter
currentProductsTable = taProduct.GetData
End Using
Dim issueCounter As Integer = 1
Dim previousRow As tblProductsRow = Nothing
Using con As SqlConnection = New SqlConnection(My.Settings.MyConnectionString)
Using cmd As New SqlCommand("UPDATE dbo.tblProducts SET [intIssue] = @issueCounter WHERE pkProduct = @ProductID", con)
cmd.Connection = con
con.Open()
For Each row As tblProductsRow In currentProductsTable.Rows
If Not previousRow Is Nothing Then
If row.fkProductID = previousRow.fkProductID Then
cmd.Parameters.Clear()
cmd.Parameters.Add("@issueCounter", SqlDbType.Int).Value = issueCounter
cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = row.pkProduct
previousRow = row
Else
issueCounter = 1
cmd.Parameters.Clear()
cmd.Parameters.Add("@issueCounter", SqlDbType.Int).Value = issueCounter
cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = row.pkProduct
previousRow = row
End If
Else
issueCounter = 1
cmd.Parameters.Clear()
cmd.Parameters.Add("@issueCounter", SqlDbType.Int).Value = issueCounter
cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = row.pkProduct
previousRow = row
End If
cmd.ExecuteNonQuery()
issueCounter += 1
Next
End Using
End Using
我已经订购了.GetData
,fkProductID ASC
和intIssue ASC
,但需要很长时间,因为 . 中大约有 30000rows
个table
。
我想知道是否有更快的方法来进行这种更新?
解决方案
我猜你正在使用 SQL Server。如果是这样,您可以在一个查询中完成所有操作:
with toupdate as (
select p.*,
row_number() over (partition by fkProductID order by pkProduct) as new_intIssue
from dbo.tblProducts p
)
update toupdate
set intIssue = new_intIssue
where intIssue <> new_intIssue;
推荐阅读
- c++ - 多个 if-else 测试的更简单方法
- python - 将 3 通道 RGB 图像转换为 1 通道标签图像
- javascript - 通过将 javascript 变量传递给 ruby 方法,将 ruby 数组迭代到 javascript 数组
- ruby-on-rails - Sidekiq 在两者之间无缘无故地杀死服务器 RAM 约为 8GB
- c++ - 尝试构建 Skia 时缺少头文件
- python - TensorFlow 不能使用 GPU。tf.test.is_gpu_available() 显示 GPU 但不能使用
- ruby-on-rails - Rails CSV 导入 - 文件名太长 @ rb_sysopen
- sql - 存储过程的 SQL 分离
- mysql - 我如何将mysql连接到python
- javascript - 如何从json获取具有动态变量的对象值