Renumber column in table

RIAS
RIAS used Ask the Experts™
on
Hi,
How do I change the column number in a serial way
example:
1,2,3
excluding which have a ref number :  0

example
Refno
1
2
3
0
5
0
6

The expected result:


Refno
1
2
3
0
4
0
5

Thanks in advance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
If you create a query that excludes RefNo=0, you can use RefNo as the Priority value as used here:

Access, form list, re-order priority

Author

Commented:
Gustav,
Can you please give a sql query.

Thanks
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
So, what you are saying is that you want a SQL statement which will renumber the values in the RefNo colum so that they are sequential (except for the zeros)?

Does your table contain an identity (autonumber) column?
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
This query will give you the numbers:

SELECT 
    RefNo,
    (Select Count(*) From YourTable As T Where T.RefNo <> 0 And T.RefNo <= YourTable.RefNo) AS Sequence
FROM 
    YourTable
WHERE 
    ID<>0;

Open in new window

Write those to a (temp) table and use that for updating your main table.
Why do we need the ID column?
CREATE TABLE MyTbl (refno int)
INSERT INTO MyTbl VALUES (1), (2), (3), (0), (5), (0),(6)

SELECT * FROM MyTbl

SELECT refno, CASE refno WHEN 0 THEN 0 ELSE ROW_NUMBER() OVER (ORDER BY CASE refno WHEN 0 THEN NULL ELSE -refno END DESC) END rn FROM MyTbl

Open in new window

Of course, the row order on output is random when you don't specify ORDER BY clause in the query
And the update itself could look like this code (when non-zero refno values are unique)
BEGIN TRAN
UPDATE MyTbl SET refno = n.rn
  FROM MyTbl t
  JOIN (
        SELECT refno, ROW_NUMBER() OVER (ORDER BY refno) rn FROM MyTbl WHERE refno <> 0
	   ) n ON n.refno = t.refno
SELECT * FROM MyTbl

Open in new window

Add COMMIT or ROLLBACK based on the result satisfaction...

Author

Commented:
Thanks, will try that but I am not able to assign value '0' to a ref column where 'isdeleted' ='1'

My table  script is as follows:

CREATE TABLE table1 (
      [Registration] [nvarchar](255) NOT NULL,      
      [EstFinishDate] [date] NULL,
      [Resolution] [nvarchar](max) NULL,
      [Ref] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
(
      [Registration] ASC,
      [Ref] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Getting error:

Cannot update identity column 'Ref'.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
No, you can't update the PK this way. Use another field as PK (autonumber).
You should not use IDENTITY column or even PK for descriptive data or for data which do have meaning for users.

PK is used to have unique row identification internaly in the application which uses this table. Table JOINs are one of the examples.
IDENTITY is one of the ways which ensures unique values in certain column.
You should never change PKs as it could have bad impact to the relational data integrity.

You should add a new column for ordering purposes and this column can contain whatever users ask for.

Author

Commented:
But its a composite key.
[Registration] ASC,
      [Ref] ASC
are composite key.
If not how can I make it composite
Composite primary keys are OK but you have to rethink PK use for your goals...

You may achieve the identity column renumbering the following way:
1) add a new column for numbers generation
2) fill the new column
3) drop the identity column
4) create it again without identity
5) fill the values
6) and finally alter the table and add the identity again to the column again...

Of course, you have to DROP and recreate the PK as a part of the above steps...

So to create a new table, fill it by required data, and rename it seems to be easier work...

And even better is to use the additional column permanently and leave identity as is if you need to present some consecutive numbering on UI.

Author

Commented:
Thanks experts!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial