Update Each Exisiting Row of SQL Server Table Using Loop

Question

I'm trying to update a certain column for each row in my SQL Server table, using ASP.NET, C#.

When this code is executed, I want the OrderNo of the first row in the column to be = 1. From then on, I want the OrderNo to increment by 1 for the other existing rows in the table.

At the moment, I can update the OrderNo of one row, however I can't get a loop to properly read through all the rows & increment as I would like.

Here is the structure of my table

Here

And below is my C#:

 con.Open();
 SqlCommand cmdUpdateOrderNo;
 cmdUpdateOrderNo = new SqlCommand("UPDATE tblImages SET [OrderNo]=@O WHERE [OrderNo] = 2;", con);
 cmdUpdateOrderNo.Parameters.AddWithValue("@O", 4);
 cmdUpdateOrderNo.ExecuteNonQuery();

Here is the reason I need to update the OrderNo:

  protected void Timer1_Tick(object sender, EventArgs e)
{
    int i = (int)ViewState["ImageDisplayed"];
    i = i + 1;
    ViewState["ImageDisplayed"] = i;
    DataRow imageDataRow = ((DataSet)ViewState["ImageData"]).Tables["image"].Select().FirstOrDefault(x => x["order"].ToString() == i.ToString());
    if (imageDataRow != null)
    {
        Image1.ImageUrl = "~/MyImages/" + imageDataRow["name"].ToString();
        lblImageName.Text = imageDataRow["name"].ToString();
        lblImageOrder.Text = imageDataRow["order"].ToString();
        lblImageDesc.Text = imageDataRow["Desc"].ToString();
    }
    else
    {
        SetImageUrl();
    }
}

private void SetImageUrl()
{
    DataSet ds = new DataSet();//Creating a dataset
    SqlDataAdapter da = new SqlDataAdapter("SELECT Name, [Order], [Desc] FROM tblImages", con);
    da.Fill(ds, "image");

    ViewState["ImageData"] = ds;//storing the dataset in a ViewState variable
    ViewState["ImageDisplayed"] = 1;//storing order number of the image currently displayed

    DataRow imageDataRow = ds.Tables["image"].Select().FirstOrDefault(x => x["order"].ToString() == "1");
    Image1.ImageUrl = "~/MyImages/" + imageDataRow["name"].ToString();
    lblImageName.Text = imageDataRow["name"].ToString();
    lblImageOrder.Text = imageDataRow["order"].ToString();
    lblImageDesc.Text = imageDataRow["Desc"].ToString();
}

Why the condition WHERE OrderNo = 2? This will limit the action to rows with OrderNo = 2 (probably just one) — Steve
– Steve, Commented Jan 4, 2017 at 13:45
Re-sequencing Order Numbers seems a weird, and potentially damaging, thing to do.Can you provide some context on why you would want to do this? — Jamiec
– Jamiec, Commented Jan 4, 2017 at 13:47
There's no need to go row by row when you can update the whole table using ROW_NUMBER for instance — Lamak
– Lamak, Commented Jan 4, 2017 at 13:47
@Steve Hi, that was just to check if my SQL was updating any row — Sweg
– Sweg, Commented Jan 4, 2017 at 13:56
@Jamiec I've added additional code above to help explain. I have a slideshow which displays these images. The order in which they appear is based on the OrderNo values. The slideshow begins at 1, and then increments to display further images. The problem is that if you delete an image in the middle of the table (i.e. where OrderNo = 3), the timer stops working after 2, because it cannot find 3. The reason to update this column is that when I delete OrderNo 3, OrderNo 4 is updated to 3 so the slideshow displays all the images — Sweg
– Sweg, Commented Jan 4, 2017 at 14:03

SqlZim · Accepted Answer · 2017-01-04 14:26:19Z

3

I agree with @Jamiec on this that it seems like a bad idea, but you don't need a loop if it's really what you want to do. Here's an example using a CTE and a Window Function.

Example

WITH T AS
    (   SELECT  
            *,
            ROW_NUMBER() OVER (ORDER BY ID) as RN
        FROM    YourTable
    )

/* this shows you the results of the CTE, 
   specifically the ROW_NUMBER() 
   which will replace the OrderNo in the code below */

SELECT * FROM T

Runing This WIll Update Your Table

WITH T AS
(   SELECT  
        *,
        ROW_NUMBER() OVER (ORDER BY ID) as RN
    FROM    YourTable
)
UPDATE T
SET OrderNo = RN

edited Jan 4, 2017 at 14:26

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

answered Jan 4, 2017 at 13:56

S3S

25.2k5 gold badges29 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

James Casey · Accepted Answer · 2017-01-04 14:01:36Z

-1

When dealing with SQL you almost never want to be writing loops.

Try changing your command to this

; with cte as
(
    select *, row_number() over (order by ID) as rn 
    from tblImages
) 
update tblImages
set OrderNo = rn
from cte

edited Jan 4, 2017 at 14:01

answered Jan 4, 2017 at 13:59

James Casey

2,5071 gold badge14 silver badges19 bronze badges

3 Comments

Lamak Over a year ago

once you have the CTE there's no need to do an INNER JOIN with the table, you can do the update as in @scsimon's answer

Lamak Over a year ago

yeah, but this is essentially @scsimon's answer

James Casey Over a year ago

Convergent evolution ;)

Collectives™ on Stack Overflow

Update Each Exisiting Row of SQL Server Table Using Loop

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related