help 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 updating data

Author

Topic

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2008-05-13 : 20:18:27

Hi,

What is the best to update line_no in following code.
As you can see 1 invoice can have multiple lines.

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 1
1000 1
1000 1
2000 2
2000 2
2000 2
2000 2

Thanks

mk_garg

singularity
Posting Yak Master

153 Posts

Posted - 2008-05-13 : 20:22:54

Just taking a guess as to how the line_no field is derived...

update a
set a.line_no = a.invoicenumber / 1000
from #xx a

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2008-05-13 : 20:31:03

Hi,
These invoice numbers are just example.
I found out, i have to use DENSE_RANK.

UPDATE t
SET t.line_no = t.RowNo
FROM (SELECT ROW_DENSE_RANK() OVER (ORDER by invoicenumber) AS RowNo,
invoicenumber,
line_no
FROM #xx)t

Thanks

mk_garg

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2008-05-13 : 20:32:45

sorry

this should work

UPDATE t
SET t.line_no = t.RowNo
FROM (SELECT DENSE_RANK() OVER (ORDER by invoicenumber) AS RowNo,
invoicenumber,
line_no
FROM #xx)t

mk_garg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources