Improve company productivity with a Business Account.Sign Up

x
?
Solved

update rownumber

Posted on 2013-06-27
7
Medium Priority
?
260 Views
Last Modified: 2013-06-27
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?
0
Comment
Question by:johnson1
  • 4
  • 2
7 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39281451
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
 
LVL 4

Expert Comment

by:dswatt
ID: 39281463
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
 

Author Comment

by:johnson1
ID: 39281532
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 66

Expert Comment

by:Jim Horn
ID: 39281557
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
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 39281566
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
 

Author Closing Comment

by:johnson1
ID: 39282993
Thank you.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39283001
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

606 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question