updating item postions in a folder table

• krul

  SSC Enthusiast

  Points: 104

  More actions

  June 14, 2009 at 12:34 pm

  #135104

  Hi,

  I have a table that has UNIQUE NONCLUSTERED INDEX on two fields FolderId & ItemPosition.

  Now I'm in situation that I have multiple rows for one folderId. Each row should have unique position.

  Lets say now I want to update all folder items position (user reorder items on interface) without running into violating index.

  I was thinking to take following approach prior to updating folder items one by one:

  I would decrease position for 1000 which would as a result affect position values to negative number (No folder have more than 10 items). After that I would update positions one by one without violating unique index.

  Somehow I feel that my idea is not the most civilized way to deal with this problem.

  Hopefully somebody can offer better model?

• Gail Shaw

  SSC Guru

  Points: 1004514

  More actions

  June 14, 2009 at 2:08 pm

  #1009388

  Not sure if I'm understanding you clearly. You want to increase all of the itempositions by 1? If so a simple update will work.

  UPDATE SomeTable SET ItemPosition = ItemPosition+1

  That won't violate any constraints as the update is done as a single operation and there will never be a situation where half of the rows are updated.

  If that's not what you want to do then please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  June 14, 2009 at 3:03 pm

  #1009391

  Dave Portas , SQL Server MVP, has a solution for this type of problem that has been published in Joe Celko's "SQL For Smarties" in Chapter 24.

  CREATE TABLE Runs

  (seq_nbr INTEGER NOT NULL PRIMARY KEY,

  val INTEGER NOT NULL);

  DECLARE@old_seq_nbr integer

  ,@new_seq_nbr integer

  -- set to appropriate values

  SET@old_seq_nbr = 20

  SET@new_seq_nbr = 33

  UPDATERuns

  SETseq_nbr =

  CASE

  WHEN seq_nbr = @old_seq_nbr THEN @new_seq_nbr

  WHEN seq_nbr BETWEEN @old_seq_nbr AND @new_seq_nbr THEN seq_nbr - 1

  WHEN seq_nbr BETWEEN @new_seq_nbr AND @old_seq_nbr THEN seq_nbr + 1

  ELSE seq_nbr

  END

  WHERE seq_nbr BETWEEN @old_seq_nbr AND @new_seq_nbr

  OR seq_nbr BETWEEN @new_seq_nbr AND @old_seq_nbr;

  SQL = Scarcely Qualifies as a Language