update all records - 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 2000 Forums

SQL Server Development (2000)

update all records

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2005-05-25 : 13:30:02

My table with these sample records:
profile_id username password
1 UserA b010
2 UserB b800
3 UserC b432

I want to update the username field to show this:
profile_id username password
1 User010 b010
2 User800 b800
3 User432 b432

What should happen is take the numeric values in the password and concatenate it to the word "User" as the username. How do I do this with just one update query?

Thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-05-25 : 13:41:54

If this won't work 100% of the time, you'll need to give some more explaination password formats:


set nocount on
declare @myTable table (profile_id int, username varchar(10), password varchar(10))
insert @myTable
select 1, 'UserA', 'b010' union
select 2, 'UserB', 'b800' union
select 3, 'UserC', 'b432'

select * from @myTable
update @myTable set password = 'User' + substring(password, 2, 99)
select * from @myTable

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources