Skip to main content
Database Administrators

Return to Question

Tweeted twitter.com/#!/StackDBAs/status/495871148396400641
deleted 71 characters in body; edited tags; edited title
Source Link
Paul White ♦
Paul White ♦
  • 90.3k
  • 30
  • 424
  • 663

update N Adding a sequence number to existing rows with performance considerations

I have a table with OrderSequence, thean OrderSequence column. The schema looks roughly like this:

  • id
  • Name
  • OrderSequence
id 
Name 
OrderSequence

I have an arraysarray of ids ["1", "2", "3", "4"] from an application, iand I want to set the SequenceOrderOrderSequence for sorting purposepurposes. I don't want to loop it viathrough the rows from the application and execute N number of sql, executing 'n' SQL queries one at a time. How should ican I do itthis using a stored proceduresprocedure?

Now the looks like

Sample data

id Name OrderSequence
1  A    0
2  B    0
3  C    0

What i want to achieve

Desired result

id Name OrderSequence
1  A    1
2  B    2
3  C    3

Using a stored procedure is ideal or if there's a way to update this in one sql command?

update N rows with performance considerations

I have a table with OrderSequence, the schema looks roughly like this:

  • id
  • Name
  • OrderSequence

I have an arrays of ids ["1", "2", "3", "4"] from application, i want to set the SequenceOrder for sorting purpose. I don't want to loop it via the application and execute N number of sql queries. How should i do it using stored procedures?

Now the looks like

id Name OrderSequence
1  A    0
2  B    0
3  C    0

What i want to achieve

id Name OrderSequence
1  A    1
2  B    2
3  C    3

Using a stored procedure is ideal or if there's a way to update this in one sql command?

Adding a sequence number to existing rows

I have a table with an OrderSequence column. The schema looks roughly like this:

id 
Name 
OrderSequence

I have an array of ids ["1", "2", "3", "4"] from an application, and I want to set the OrderSequence for sorting purposes. I don't want to loop through the rows from the application, executing 'n' SQL queries one at a time. How can I do this using a stored procedure?

Sample data

id Name OrderSequence
1  A    0
2  B    0
3  C    0

Desired result

id Name OrderSequence
1  A    1
2  B    2
3  C    3
Source Link
Lee Gary
Lee Gary
  • 153
  • 1
  • 1
  • 5

update N rows with performance considerations

I have a table with OrderSequence, the schema looks roughly like this:

  • id
  • Name
  • OrderSequence

I have an arrays of ids ["1", "2", "3", "4"] from application, i want to set the SequenceOrder for sorting purpose. I don't want to loop it via the application and execute N number of sql queries. How should i do it using stored procedures?

Now the looks like

id Name OrderSequence
1  A    0
2  B    0
3  C    0

What i want to achieve

id Name OrderSequence
1  A    1
2  B    2
3  C    3

Using a stored procedure is ideal or if there's a way to update this in one sql command?