Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
yetti
Starting Member
1 Post |
 Posted - 2007-09-26 : 20:45:03
|
| Hi all, I've been a lurker on SQL Team for a LOOOONG time and finally have a situation where I'd simply like some advice. I have a Resources table that stores picture data for one of our applications. One of the fields is sortOrder. If someone deletes the fifth picture, I would like to run an update on the delete trigger that will go through and reset the sort orders for the remaining photos correctly.id_picture id_asset assetType sortOrder-----------------------------------------------------1234 17 'Rolex' 11235 17 'Rolex' 21236 17 'Rolex' 31237 17 'Rolex' 41238 17 'Rolex' 51239 17 'Rolex' 61240 17 'Rolex' 7If you remove picture 1237, what is the best methodology for efficiently updating the remaining so that you getid_picture id_asset assetType sortOrder-----------------------------------------------------1234 17 'Rolex' 11235 17 'Rolex' 21236 17 'Rolex' 31238 17 'Rolex' 41239 17 'Rolex' 51240 17 'Rolex' 6I've looked into both cursors and while loops, was hoping someone with far more expertise can give me some insight. Code or a tutorial to point me in the correct direction would be very very helpful! TIA! |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-09-26 : 20:56:07
|
do you need to store the ordering in the table ?You can use row_number() over (order by <your ordering column list here>) for the sortOrder
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-09-26 : 21:02:10
|
quote:
Originally posted by jsmith8858
If it is a sort order column, having a missing value will have no effect.- Jeffhttp://weblogs.sqlteam.com/JeffS
Good point 
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
Reorder of sortOrder column