SQL Select and update

[Nikon70]

I have a sqlite database and want to update the sort column in my table.

e.g.

Col1 - Col2
C - 4
A - 3
B - 5

I want to sort on col1 then update Col2 with new values i.e. 1-3

Don't know if it's possible but doing it in code by looping and doing individual writes is a little slow.

[pritesh2010]

huun you can a sort a column using OrderBy clause
Select * from tmpls order by col1
and then you can fire a update query based on that column
UPdate tmpls Set col2=1 Where col1 ='A'

[Nikon70]

Thanks for the reply but I want to do it all in one go using a sub query. So the update needs to be able to increment the value inserted into the sort column.

[leahrose87]

hmmm you can try this:

Dim dr As DataRow
Dim dt As New DataTable
Dim da As New SqlDataAdapter
Dim sqlSelect As String = "Select * from tableName order by col1"
Dim myConnection as New SqlConnection(Connection) 'Create a function that will return the connection String
Dim mycommand as New SqlCommand(sqlSelect,myConnection)
da.SelectCommand = myCommand
myConnection.Open()
da.Fill(dt)
myConnection.Close()
Dim i as integer = 1
For Each dr As DataRow In dt.Rows
       Dim sqlUpdate As String = "Update tableName set col2='"& i &"' where col1='" & dr("col1") &"'"
Next

I didn't try this but hope it can help.

[mfas]

Hi,

I don't know SQLLite, so I can only give you a hint using SQL Server 2008 - I hope it guides you in the right direction:

UPDATE [Test]
SET [Col2] = [t2].[RowNum]
FROM [Test] AS [t1]
JOIN (SELECT [Col1], ROW_NUMBER() OVER (ORDER BY [Col1]) AS [RowNum]
      FROM [Test]) AS [t2] ON [t1].[Col1] = [t2].[Col1]

The table in question is called "Test" and can be replaced by any name of your choosing.