August 24, 2017 at 5:18 am
i have a table called dbo.sheet3 with 7 fields and 75 records
I made a new column called NO and its for the total row number of the column
how to insert (1,2,3... 75 ) in the NO column using row number function
Regards
August 24, 2017 at 5:23 am
zorbatouche - Thursday, August 24, 2017 5:18 AMi have a table called dbo.sheet3 with 7 fields and 75 records
I made a new column called NO and its for the total row number of the column
how to insert (1,2,3... 75 ) in the NO column using row number functionRegards
How do you want the data sorted?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 24, 2017 at 5:32 am
Phil Parkin - Thursday, August 24, 2017 5:23 AMzorbatouche - Thursday, August 24, 2017 5:18 AMi have a table called dbo.sheet3 with 7 fields and 75 records
I made a new column called NO and its for the total row number of the column
how to insert (1,2,3... 75 ) in the NO column using row number functionRegards
How do you want the data sorted?
i wrote a query as below ,
but it inserted all the NO rows as 1 UPDATE DBO.Sheet3$
SET DBO.Sheet3$.NO =T.O from
(SELECT ROW_NUMBER () OVER (ORDER BY DESCRIPTION ASC ) AS O
FROM DBO.SHEET3$) AS T
i would like to sort according to description column
August 24, 2017 at 5:43 am
You need to join back to your subquery on the table's PK or unique key. Here is an example:
CREATE TABLE #test1
(
Id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY
, rn INT
, Dsc VARCHAR(50)
);
INSERT #test1
(
Dsc
)
SELECT 'Some text'
UNION ALL
SELECT 'Aardvark';
SELECT *
FROM #test1;
WITH Ordered
AS
(
SELECT
t.Id
, rn = ROW_NUMBER() OVER (ORDER BY t.Dsc)
FROM #test1 t
)
UPDATE t
SET t.rn = Ordered.rn
FROM
#test1 t
JOIN Ordered ON t.Id = Ordered.Id;
SELECT *
FROM #test1 t;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 24, 2017 at 5:51 am
Phil Parkin - Thursday, August 24, 2017 5:43 AMYou need to join back to your subquery on the table's PK or unique key. Here is an example:
CREATE TABLE #test1
(
Id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY
, rn INT
, Dsc VARCHAR(50)
);INSERT #test1
(
Dsc
)
SELECT 'Some text'
UNION ALL
SELECT 'Aardvark';SELECT *
FROM #test1;WITH Ordered
AS
(
SELECT
t.Id
, rn = ROW_NUMBER() OVER (ORDER BY t.Dsc)
FROM #test1 t
)
UPDATE t
SET t.rn = Ordered.rn
FROM
#test1 t
JOIN Ordered ON t.Id = Ordered.Id;SELECT *
FROM #test1 t;
Can we do this in a simple update statement
August 24, 2017 at 5:57 am
The UPDATE statement is this bit:WITH Ordered
AS
(
SELECT
t.Id
, rn = ROW_NUMBER() OVER (ORDER BY t.Dsc)
FROM #test1 t
)
UPDATE t
SET t.rn = Ordered.rn
FROM
#test1 t
JOIN Ordered ON t.Id = Ordered.Id;
Aside from the JOIN. it is precisely as 'simple' as the one you provided. So please define what you mean by simple.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 24, 2017 at 6:29 am
Phil Parkin - Thursday, August 24, 2017 5:57 AMThe UPDATE statement is this bit:WITH Ordered
AS
(
SELECT
t.Id
, rn = ROW_NUMBER() OVER (ORDER BY t.Dsc)
FROM #test1 t
)
UPDATE t
SET t.rn = Ordered.rn
FROM
#test1 t
JOIN Ordered ON t.Id = Ordered.Id;
Aside from the JOIN. it is precisely as 'simple' as the one you provided. So please define what you mean by simple.
This is a simple statement and i also believe it can be done with a merge statement even.
August 24, 2017 at 6:45 am
Phil Parkin - Thursday, August 24, 2017 5:57 AMThe UPDATE statement is this bit:WITH Ordered
AS
(
SELECT
t.Id
, rn = ROW_NUMBER() OVER (ORDER BY t.Dsc)
FROM #test1 t
)
UPDATE t
SET t.rn = Ordered.rn
FROM
#test1 t
JOIN Ordered ON t.Id = Ordered.Id;
Aside from the JOIN. it is precisely as 'simple' as the one you provided. So please define what you mean by simple.
I tried the below query on NO column but it inserted another 75 null records + 75 rows with NO column 1
INSERT DBO.Sheet3$ (NO)
SELECT P FROM
(SELECT ROW_NUMBER () OVER (ORDER BY DESCRIPTION ASC ) P
FROM DBO.Sheet3$ ) K
Here i am only inserting into a single column but still it created extra rows?
August 24, 2017 at 7:10 am
zorbatouche - Thursday, August 24, 2017 6:45 AMPhil Parkin - Thursday, August 24, 2017 5:57 AMThe UPDATE statement is this bit:WITH Ordered
AS
(
SELECT
t.Id
, rn = ROW_NUMBER() OVER (ORDER BY t.Dsc)
FROM #test1 t
)
UPDATE t
SET t.rn = Ordered.rn
FROM
#test1 t
JOIN Ordered ON t.Id = Ordered.Id;
Aside from the JOIN. it is precisely as 'simple' as the one you provided. So please define what you mean by simple.I tried the below query on NO column but it inserted another 75 null records + 75 rows with NO column 1
INSERT DBO.Sheet3$ (NO)
SELECT P FROM
(SELECT ROW_NUMBER () OVER (ORDER BY DESCRIPTION ASC ) P
FROM DBO.Sheet3$ ) KHere i am only inserting into a single column but still it created extra rows?
You need a join between dbo.Sheet3$ and K, on the PK of the table.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 24, 2017 at 11:42 am
Phil Parkin - Thursday, August 24, 2017 7:10 AMzorbatouche - Thursday, August 24, 2017 6:45 AMPhil Parkin - Thursday, August 24, 2017 5:57 AMThe UPDATE statement is this bit:WITH Ordered
AS
(
SELECT
t.Id
, rn = ROW_NUMBER() OVER (ORDER BY t.Dsc)
FROM #test1 t
)
UPDATE t
SET t.rn = Ordered.rn
FROM
#test1 t
JOIN Ordered ON t.Id = Ordered.Id;
Aside from the JOIN. it is precisely as 'simple' as the one you provided. So please define what you mean by simple.I tried the below query on NO column but it inserted another 75 null records + 75 rows with NO column 1
INSERT DBO.Sheet3$ (NO)
SELECT P FROM
(SELECT ROW_NUMBER () OVER (ORDER BY DESCRIPTION ASC ) P
FROM DBO.Sheet3$ ) KHere i am only inserting into a single column but still it created extra rows?
You need a join between dbo.Sheet3$ and K, on the PK of the table.
A database is not a spreadsheet. Rows and columns are not interchangeable like they are in spreadsheets. The INSERT command inserts records (rows) and specifies which fields (columns) in those new records to populate (unspecified columns are populated with the value specified by their corresponding DEFAULT constraints or NULL if there are no DEFAULT constraints).
Secondly, Phil is wrong that you need a join. What you should be doing is updating the table through the CTE.WITH Ordered
AS
(
SELECT
t.Id, t.NO
, rn = ROW_NUMBER() OVER (ORDER BY t.Dsc)
FROM #test1 t
)
UPDATE Ordered
SET NO = rn
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 24, 2017 at 11:51 am
Secondly, Phil is wrong that you need a join. What you should be doing is updating the table through the CTE.
Damn it, why do I always forget this technique. Thanks, Drew.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 25, 2017 at 1:51 am
Phil Parkin - Thursday, August 24, 2017 11:51 AMSecondly, Phil is wrong that you need a join. What you should be doing is updating the table through the CTE.
Damn it, why do I always forget this technique. Thanks, Drew.
you were not alone here phil .
I did the same mistake above using insert comment to update the data.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply