Update query Help

Question

Update query Help

KGJ-Dev

SSCertifiable

Points: 5780
More actions
January 6, 2016 at 12:44 pm

#323369

Hi,
I have a table called Users and have column called Name. The table has 50000 rows and i want to update the name column as 'Sample'+sequence number like below
Sample1
Sample2
Sample3.....
Any sample query please. I checked the internet and all sample based on the int column. I am looking for varchar column. Any help please..

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

KGJ-Dev (1/6/2016)
Hi,
I have a table called Users and have column called Name. The table has 50000 rows and i want to update the name column as 'Sample'+sequence number like below
Sample1
Sample2
Sample3.....
Any sample query please. I checked the internet and all sample based on the int column. I am looking for varchar column. Any help please..

I assume that not every row in your table has the same value for Name? What do you want to use for your ordering criteria? And yes you have to generate a number but you can cast that to varchar. Help us to help you by providing some details.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Here is a pretty generic example of how you might do this.

if OBJECT_ID('tempdb..#Users') is not null

drop table #Users

create table #Users

(

Name varchar(50)

)

insert #Users(Name)

values

('Francisco'),

('Ashley'),

('Samuel'),

('Georgia'),

('Bernice'),

('Thomas'),

('Janice'),

('James'),

('Martha'),

('Marvin');

with NumberedValues as

(

select Name, ROW_NUMBER() over(order by Name) as RowNum

from #Users

)

update NumberedValues

set Name = Name + CAST(RowNum as varchar(3));

select * from #Users;

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 3

Hi Sean,

Thanks for your reply and jsut now i finished my requirement with rownumber as like your logic. I thought of doing it without the rownumber logic. Finally I end up with using that. Thanks for your reply too... Appreciated your time on this post

Thanks