update rownumber

Hello,
I would like to add rownumber in a column.

1) Can I add it in insert when I use select? For example:
INSERT INTO @TempTable
SELECT * FROM @TempTable2

2) If I try to update it like this:
UPDATE t SET Num=ROW_NUMBER() OVER(ORDER BY (SELECT 0))
FROM @Temp2 t

Then I get the error:
Windowed functions can only appear in the SELECT or ORDER BY clauses.

What is the best approach?
johnson1Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
To do ROW_COUNT as an update, you'll need to throw it into a subquery:

-- Delete the row_count
UPDATE #tgt
SET row_count = 0

-- Re-create the rowcount
UPDATE #tgt
SET row_count = rc.row_count
FROM #tgt
	JOIN (SELECT col1, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) as row_count FROM #src) rc ON #tgt.col1 = rc.col1

-- Victory.
SELECT '#tgt'
SELECT * FROM #tgt

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
1)  Yes, although since you're using an expression you'll have to explicitly state all columns

INSERT INTO @Whatever (col1, col2, col3, rownumber)
SELECT col1, col2, col3, ROW_NUMBER() OVER (ORDER BY col1)
FROM @whatever_2

2)  Probably didn't like the SELECT 0, replace it with SELECT { any column(s) }
0
 
dswattCommented:
There is a rownumber function in SQL that may already meet your needs this article explains it very nicely, may save you a bit of time

http://www.techpint.com/programming/row-number-function-sql-server
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
johnson1Author Commented:
Hi,
1) If  I add Row_number in select statement then the rownumber is 1 for all the rows I insert.
2) I do not want to change the order of the rows and therefor I use Select 0. It works fine when I use it in a select statement.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I see where you're going with SELECT 0.  I was able to get it to work as an INSERT in SSMS with the below code:

CREATE TABLE #src (col1 varchar(10))
CREATE TABLE #tgt (col1 varchar(10), row_count int)

INSERT INTO #src (col1)
VALUES ('apple'), ('orange'), ('grape'), ('watermelon'), ('raspberry'), ('kiwi')

INSERT INTO #tgt (col1, row_count)
SELECT col1, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) as row_count
FROM #src

SELECT '#src'
SELECT * FROM #src

SELECT '#tgt'
SELECT * FROM #tgt

Open in new window

0
 
johnson1Author Commented:
Thank you.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0
All Courses

From novice to tech pro — start learning today.