Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

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?
0
johnson1
Asked:
johnson1
  • 4
  • 2
1 Solution
 
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
 
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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
 
Jim HornMicrosoft 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
 
johnson1Author Commented:
Thank you.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now