Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2014

    Unanswered: How to Re number integer field values in a table?(not identity/primary key column)

    Hello Everyone,

    I am facing problem with re numbering of field records in a table. please help me. I am having one table with below records.

    sid(identity/primarykey) stickyId(Integer) UserId(integer)
    102 0 171
    103 1 171
    104 2 171
    105 3 171

    here how to renumbering stickyId values when deleted particular stickyId from UI. Here stickyId field is Integer type only. not primarykey/identity field

    Any suggestions would be thankful.

    Thanks in Advance
    Satish Chandragiri

  2. #2
    Join Date
    Jan 2007
    Provided Answers: 12
    The answer is that you don't renumber as there is no need.
    If you want to generate a sequential numbering for you application, do it on the fly e.g.
    SELECT sid
         , stickyid As actual_stickyid
         , Row_Number() OVER (PARTITION BY userid ORDER BY stickyid) As sequence
         , userid
    FROM   your_table
    Home | Blog

  3. #3
    Join Date
    Jan 2013
    Provided Answers: 1

    You do not know basic concepts and terms

    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums. What little you did post is wrong. Rows are not records. Columns are not fields. Tables have no ordering. This is in the first 1-3 chapters of any book on RDBMS.

    The MS proprietary IDENTITY table property (it is not a column) cannot be a valid by definition. Since we do not do math on identifiers, they are seldom (if ever!) a numeric in a properly designed schema.

    You can use a function call
    ROW_NUMBER() OVER (ORDER BY something_id) AS sticky_seq

    This will always be correct, not waste disk space, etc. Now, if you had a Netflix queue and anted to re-arrange it, that is a different problem.

  4. #4
    Join Date
    Apr 2004
    First of all I personally do not like to delete rows unless it is absolutely necessary for just this reason. I simply mark the row as deleted and exclude it from my queries so that the data is always there and prevent parent child orphan relation problems down the road. After all Disk Space is free these days and it is better to keep the data then to get rid of it.

    What is Stick ID used for? Is this a label that sows in some report some were or is it a foreign key? I agree with Celko that we need you DDL so we can see what the data elements are.

    Are you going to be doing this renumbering in a trigger of some kind? If so do you already have an update trigger and if you do can you post it?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts