Update - 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)

Update

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-03-25 : 17:17:21

Hello, I have to update Field2, look at the output looks like.
declare @tbl1 table
(ID INT,
Field1 varchar(20),
Field2 varchar(10)
)

INSERT INTO @tbl1
SELECT 1, 'AAA',null

INSERT INTO @tbl1
SELECT 2,'AAA', null

INSERT INTO @tbl1
SELECT 3,'AAA', null

INSERT INTO @tbl1
SELECT 4, 'BBB',null

INSERT INTO @tbl1
SELECT 5,'BBB', null

INSERT INTO @tbl1
SELECT 6,'CCC', null

INSERT INTO @tbl1
SELECT 7,'CCC', null

select * from @tbl1

Out Put :
ID Field1 Field2
1 AAA 1001
2 AAA 1002
3 AAA 1003
4 BBB 1001
5 BBB 1002
6 CCC 1001
7 CCC 1002

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-25 : 18:30:06

[code]
update t
set Field2 = convert(varchar(10), 1000 + row_no)
from (
select ID, Field1, Field2,
row_no = row_number() over (partition by Field1 order by ID)
from @tbl1
) t
[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-26 : 02:29:24

I suggest you to do it when you display data using SELECT statement
Otherwise you need to run updates as soon as new data are added

Madhivanan

Failing to plan is Planning to fail

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-26 : 02:55:03

And if permanently you want to update then you should also make the same logic at the time of insert i think.

Vaibhav T

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-03-26 : 09:19:22

Thanks it works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources