根据行中的值用值更新SQL行的最快方法是什么? [英] What is the fastest way to update SQL rows with values, based upon a value in the row?

查看:63
本文介绍了根据行中的值用值更新SQL行的最快方法是什么?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在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 ASCintIssue 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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆