Solved

update rownumber

Posted on 2013-06-27
7
247 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 65

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
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
LVL 65

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 65

Accepted Solution

by:
Jim Horn earned 500 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 65

Expert Comment

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

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Results to Excel File 18 90
SQL - format decimal in a string 5 56
SQL trigger 5 51
Getting local user timezone in Sql Server 5 40
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

752 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