Re-Order Numbers in Table

• Stylez

  SSCrazy

  Points: 2195

  More actions

  February 11, 2013 at 1:54 pm

  #289257

  Good Afternoon Guys.

  would it be possible if you could give me a Hand in solving my problem?

  i have A table That is Consisting of these fields below

  Container AttributeTypeID priority Sequence Branch ID

  4 1 3

  11 2 3

  10 3 3

  What i need to do is if i change The Priority Sequence using The Branch ID and Container Attribute ID as my where clause, i must do updates on all the Priority Sequence since there should be no duplicate priority Sequence. the new order of the table should look like the one below.

  Container AttributeTypeID priority Sequence Branch ID

  11 1 3

  10 2 3

  4 3 3

  i have tried using the query below but does not work. declare @branch_id int

  declare @ctr int

  declare @lowerLimit int

  declare @priority_sequence int

  declare @container_attribute_type_id int

  declare @MinimumContainerAttributeTypeID int

  set @branch_id = 5

  set @priority_sequence = 1

  set @container_attribute_type_id = 4

  set @MinimumContainerAttributeTypeID = (select Min(container_attribute_type_id) from container_attribute_type where branch_id = 5)

  print @MinimumContainerAttributeTypeID

  set @lowerLimit = 1

  set @ctr = (select COUNT(*) from container_attribute_type where branch_id = @branch_id)

  while @lowerLimit <= @ctr

  begin

  update container_attribute_type

  set

  priority_sequence= @priority_sequence,

  updated=GETDATE(),

  updatedby='nisip'

  where

  container_attribute_type_id = @container_attribute_type_id and branch_id = @branch_id

  set @lowerLimit = @lowerLimit + 1

  set @priority_sequence = @priority_sequence + 1

  set @container_attribute_type_id = @container_attribute_type_id + 1

  End

  Any ideas on this guys?

  Best Regards,

  Noel Eman Isip

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 11, 2013 at 2:03 pm

  #1586734

  supplying the missing DDL and DML setup that is preventing analysis:

  drop table container_attribute_type

  CREATE TABLE container_attribute_type(container_attribute_type_id int,priority_sequence int,branch_id int,updated datetime,updatedby varchar(30))

  INSERT INTO container_attribute_type(container_attribute_type_id,priority_sequence,branch_id)

  SELECT 4,1,3 UNION ALL

  SELECT 11,2,3 UNION ALL

  SELECT 10,3,3

  DECLARE @ctr INT

  DECLARE @lowerLimit INT

  DECLARE @priority_sequence INT

  DECLARE @container_attribute_type_id INT

  DECLARE @MinimumContainerAttributeTypeID INT

  DECLARE @branch_id INT

  SET @branch_id = 3

  SET @priority_sequence = 1

  SET @container_attribute_type_id = 4

  SET @MinimumContainerAttributeTypeID = (SELECT

  MIN(container_attribute_type_id)

  FROM container_attribute_type

  WHERE branch_id = 5)

  PRINT @MinimumContainerAttributeTypeID

  SET @lowerLimit = 1

  SET @ctr = (SELECT

  COUNT(*)

  FROM container_attribute_type

  WHERE branch_id = @branch_id)

  WHILE @lowerLimit <= @ctr

  BEGIN

  UPDATE container_attribute_type

  SET priority_sequence = @priority_sequence,

  updated = GETDATE(),

  updatedby = 'nisip'

  WHERE container_attribute_type_id = @container_attribute_type_id

  AND branch_id = @branch_id

  SET @lowerLimit = @lowerLimit + 1

  SET @priority_sequence = @priority_sequence + 1

  SET @container_attribute_type_id = @container_attribute_type_id + 1

  END

  select * from container_attribute_type

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  February 11, 2013 at 2:18 pm

  #1586737

  Thanks for the setup Lowell.

  There is no need for a cursor here. A cte will work just fine here. The actual rules are pretty vague but I think this is pretty close.

  ;with cte as

  (

  select *, ROW_NUMBER() over(partition by Branch_ID order by container_attribute_type_id desc) as RowNum

  from container_attribute_type

  )

  update cte set priority_sequence = RowNum

  select * from container_attribute_type order by priority_sequence

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/