CODEWITHSUNDEEP
sqlsql-servervb.netvisual-studio
Kevin
Kevin

Reputation: 45

SQL Server query: I need to increment an integer based on queries and set it

I am facing the following challenge: I have a SQL command in VB.NET that is used to set unique IDs on a SQL Server table.

It looks like this:

Public Async Function CreateWiedervorlagenID() As Threading.Tasks.Task

    Dim sqlquery As String = "select distinct [id_doc], [label], [date] from [reminder]"
    Dim i As Integer = 0

    Using dbConn As New SqlConnection With {.ConnectionString = conStr}
        If Not dbConn.State.Equals(ConnectionState.Open) Then Await dbConn.OpenAsync()
            Using dbCommand As New SqlCommand With {.CommandText = sqlquery, .Connection = dbConn}
                Using dbReader As SqlDataReader = Await dbCommand.ExecuteReaderAsync
                    If dbReader.HasRows.Equals(True) Then
                        While Await dbReader.ReadAsync
                            i += 1

                            Await DoChanges(i, CInt(dbReader.Item(0)), dbReader.Item(1).ToString(), dbReader.Item(2).ToString())
                        End While
                    End If

                    dbReader.Close()
                End Using
            End Using

            If Not dbConn.State.Equals(ConnectionState.Closed) Then dbConn.Close()
        End Using
End Function

Private Async Function DoChanges(id_wv As Integer, id_doc As Integer, label As String, r_date As String) As Threading.Tasks.Task
    Dim sqlquery As String = $"update [reminder] set [id_wv] = {id_wv} where [id_doc] = {id_doc} and [label] = '{label}' and [date] = '{r_date}'"

    Using dbConn As New SqlConnection With {.ConnectionString = conStr}
        If Not dbConn.State.Equals(ConnectionState.Open) Then Await dbConn.OpenAsync()

        Using dbCommand As New SqlCommand With {.CommandText = sqlquery, .Connection = dbConn}
            Try
                Await dbCommand.ExecuteScalarAsync
            Catch ex As DbException
                Debug.WriteLine($"Fehler bei DoChanges; Fehlerbeschreibung: {ex.Message}")
                MsgBox($"Fehler bei DoChanges; Fehlerbeschreibung: {ex.Message}")
            End Try
        End Using

        If Not dbConn.State.Equals(ConnectionState.Closed) Then dbConn.Close()
    End Using
End Function

And if possible, I should rewrite this code to SQL. Unfortunately, I am still in training and have little knowledge of SQL, especially since this seems to be a rather difficult SQL command. I would definitely be very grateful for any help.

Upvotes: 1

Views: 78

Answers (1)

Ranjit Singh
Ranjit Singh

Reputation: 3735

You can use row_number method to get the index and then you can simply update your id_wv column

update [reminder] set [id_wv] = IndexNumber from [reminder] as table1 inner join
(select [id_doc], [label], [date], ROW_NUMBER() over(order by id_doc) as IndexNumber from [reminder] 
group by [id_doc], [label], [date]) as table2 on  table1.[id_doc] = table2.[id_doc] 
and table1.[label] = table2.[label] and table1.[date] = table2.[date]

Upvotes: 2

Related Questions