Solved

update rownumber

Posted on 2013-06-27
7
251 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 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
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.

 
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 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 66

Expert Comment

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

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

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 …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

617 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