RIAS
asked on
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
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
ASKER
Gustav,
Can you please give a sql query.
Thanks
Can you please give a sql query.
Thanks
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?
Does your table contain an identity (autonumber) column?
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;
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
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
Add COMMIT or ROLLBACK based on the result satisfaction...
ASKER
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'.
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'.
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.
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.
ASKER
But its a composite key.
[Registration] ASC,
[Ref] ASC
are composite key.
If not how can I make it composite
[Registration] ASC,
[Ref] ASC
are composite key.
If not how can I make it composite
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks experts!
Access, form list, re-order priority