help on updating data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help on updating data

Author

Topic

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2008-05-13 : 01:33:51

Hi,

What is the best to update line_no in following code.
As you can see 1 invoice can have multiple lines.
I would like to assign sequential number to all lines in invoice.


CREATE TABLE #xx (invoicenumber INT, line_no  int )

INSERT INTO #xx VALUES(1000,0)
INSERT INTO #xx VALUES(1000,0)
INSERT INTO #xx VALUES(1000,0)
INSERT INTO #xx VALUES(1000,0)
INSERT INTO #xx VALUES(2000,0)
INSERT INTO #xx VALUES(2000,0)
INSERT INTO #xx VALUES(2000,0)
INSERT INTO #xx VALUES(2000,0)

SELECT * FROM #xx

result of select * from #xx should be:
1000 1
1000 2
1000 3
1000 4
2000 1
2000 2
2000 3
2000 4

Thanks

mk_garg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 01:58:57

[code]UPDATE t
SET t.line_no = t.RowNo
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY invoicenumber ORDER by invoicenumber) AS RowNo,
invoicenumber,
line_no
FROM #xx)t[/code]

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2008-05-13 : 02:12:22

Thanks!

mk_garg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources