根据行中的值用值更新SQL行的最快方法是什么? [英] What is the fastest way to update SQL rows with values, based upon a value in the row?
问题描述
我在SQL Server
中有以下table
,称为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
看起来像这样:
Over time, with rows having being deleted for whatever reasons, there are now gaps in the issue numbers. I would like the issue numbers to run in order so as the table
looks like this:
+-----------+--------------+-------------+
| 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
代码中使用此代码,我更新了行:
At the moment I have added the table
to my DataSet
and using this in vb.net
code i update the rows:
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
我已按fkProductID ASC
和intIssue ASC
的顺序订购了.GetData
,
但是要花很长时间,因为table
中大约有30000个rows
.
I have ordered the .GetData
by fkProductID ASC
, and intIssue ASC
,
but it takes a very long time as there are around 30000 rows
in the table
.
我想知道是否有更快的方法来进行这种更新?
I wondered if there was a faster way to do this kind of updating?
推荐答案
我将猜测您正在使用SQL Server.如果是这样,您可以在一个查询中完成所有操作:
I am going to guess that you are using SQL Server. If so, you can do this all in one query:
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;
这篇关于根据行中的值用值更新SQL行的最快方法是什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!