根据行中的值用值更新SQL行的最快方法是什么？

admin

根据行中的值用值更新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，但它需要为大约有30000很长一段时间rows的table。

我想知道是否有更快的方法来进行这种更新？

阅读 248

2021-07-01

共1个答案

admin

我猜您正在使用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;

2021-07-01