Add to a field - 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)

Add to a field

Author

Topic

michaelc80
Starting Member

3 Posts

Posted - 2007-10-31 : 13:04:04

Hi,
I have a table (table1) with 2 columns, chk_no and line_no. Currently chk_no holds check numbers and line_no holds line numbers. I need to add 1 to the line_no if more than one chk_no is found. For instance, my data currently looks like this:

chk_no line_no
2250 1
2250 1
2251 1
2252 1
2252 1

I'd like it to look like this:

chk_no line_no
2250 1
2250 2
2251 1
2252 1
2252 2
2252 3
2253 1
2254 1

So if there is more than one chk_no it should add 1 to the line_no, and increment it by 1 for each copy of the chk_no value it finds

.

Any suggestions on how to make this happen? I don't work with SQL all that often so I appreciate your help!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-31 : 13:13:44

Is there any PK column in the table ?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

michaelc80
Starting Member

3 Posts

Posted - 2007-10-31 : 14:00:19

Yes, there is a field called inv_doc_no, but that can't change. Only the line_no field.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-31 : 14:01:00

can you provide some data including that column too.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

michaelc80
Starting Member

3 Posts

Posted - 2007-10-31 : 14:05:53

It's just a doc reference number.

chk_no line_no inv_doc_no
2250.....1........1
2250.....1........2
2251.....1........3
2252.....1........4
2252.....1........5

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-31 : 14:18:43

You can create a new column, update it with the new line_no, drop the existing line_no, rename the new column to line_no.


SELECT * , ROW_NUMBER() OVER (PARTITION by chk_no ORDER BY inv_doc_no) AS NewLineNo
FROM YourTable

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-31 : 14:27:00

Could you not just leave the table alone and create a view that does the calculations for you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources