CODEWITHSUNDEEP
sqlt-sql
mohan111
mohan111

Reputation: 8865

how to update a column with same sequence

i need to update a table with another column (userID ) to Lastname Column .So i have written query like this 

update User set LastName = +'LastName_'+ CONVERT (varchar(10),UserID)

and result giving like this 

UserID    FirstName             LastName  
1               AALIYAH             Bhatt_1
2               Mohan               Kumar_2
3               varun               ratna_3
4               suresh              rania_4
5               AARON               suresh_5    

etc ......

4500            Kalyan              raju_4500
4501            raohan              manish4501

and how can i get last name in the sequence.. see the last column for example 4500 so last name is updated as raju_4500 and coming to first name userId(1) and lastname is Bhatt_1

how could i get in sequence 

UserID    FirstName             LastName  
1               AALIYAH             Bhatt_0001
2               Mohan               Kumar_0002
3               varun               ratna_0003
4               suresh              rania_0004
5               AARON               suresh_0005 

etc ......

4500            Kalyan              raju_4500
4501            raohan              manish4501

Suggest me

Upvotes: 0

Views: 67

Answers (4)

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11609

This will give the exact result.

update User set LastName = LastName +'_'+RIGHT('000'+cast(UserID as varchar), 4)

SQL Fiddle

Upvotes: 0

Vijay
Vijay

Reputation: 8461

try out this

update User set LastName = +'LastName_'+  RIGHT('0000'+ CONVERT(varchar(10),UserID),4)

Upvotes: 0

Ranish
Ranish

Reputation: 927

I think this will work

update User set LastName = +'LastName_'+  RIGHT('0000'+ CONVERT(varchar(10),UserID),4)

Upvotes: 0

Blorgbeard
Blorgbeard

Reputation: 103525

This expression gives you the length of the maximum UserID:

select len(max(userid)) from User

And you can put leading zeros on a number like this:

select right('0000000000' + convert(varchar(10), /*number*/), /*length*/)

So, putting that together, I would do this:

declare @length int = (select len(max(userid)) from User)
update User set LastName = 'LastName_' + 
    right('0000000000' + convert(varchar(10), UserID), @length)

Upvotes: 1

Related Questions