Renumber column in table

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
RIASAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
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
0
RIASAuthor Commented:
Gustav,
Can you please give a sql query.

Thanks
0
Dale FyeCommented:
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?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Gustav BrockCIOCommented:
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.
0
pcelbaCommented:
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
0
pcelbaCommented:
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...
0
RIASAuthor 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'.
0
Gustav BrockCIOCommented:
No, you can't update the PK this way. Use another field as PK (autonumber).
0
pcelbaCommented:
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.
0
RIASAuthor Commented:
But its a composite key.
[Registration] ASC,
      [Ref] ASC
are composite key.
If not how can I make it composite
0
pcelbaCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RIASAuthor Commented:
Thanks experts!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.