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.
| Author |
Topic |
|
mohan123
Constraint Violating Yak Guru
252 Posts |
 Posted - 2013-05-30 : 00:51:15
|
hi all,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_12 Mohan Kumar_23 varun ratna_34 suresh rania_45 AARON suresh_5 etc ......4500 Kalyan raju_45004501 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_1how could i get in sequence UserID FirstName LastName 1 AALIYAH Bhatt_00012 Mohan Kumar_00023 varun ratna_00034 suresh rania_00045 AARON suresh_0005 etc ......4500 Kalyan raju_45004501 raohan manish4501 P.V.P.MOhan |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-30 : 01:40:19
|
| [code]update u set LastName = LastName + '_' + RIGHT (REPLICATE('0',MaxLen) + CONVERT (varchar(10),UserID),MaxLen)FROM (SELECT *,MAX(LEN(UserID)) OVER () AS MaxLen FROM User )u[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-05-30 : 01:53:17
|
| hi while executing your query it is giving me this error Msg 209, Level 16, State 1, Line 2Ambiguous column name 'LastName'.Msg 209, Level 16, State 1, Line 3Ambiguous column name 'PatientID'P.V.P.MOhan |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-30 : 01:56:11
|
quote:
Originally posted by mohan123
hi while executing your query it is giving me this error Msg 209, Level 16, State 1, Line 2Ambiguous column name 'LastName'.Msg 209, Level 16, State 1, Line 3Ambiguous column name 'PatientID'P.V.P.MOhan
Post your actually used query. I'm sure thats different from what you showed us!------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-05-30 : 02:02:20
|
| Refer Column names with respective table names or aliasesTableA --> col1, col2 TableB --> Col1, Col2You have to specify alias/table name along with column while accessing both tables in one queryEx:TableA.col1 TableA.col2TableB.col1 TableB.col2--Chandu |
|
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-05-30 : 02:16:47
|
i have writen this query and it worked fine for me thanks for answering declare @length int = (select len(max(UserID)) from Users)update Users set LastName = 'LastName_' + right('0000000000' + convert(varchar(20), UserID), @length)P.V.P.MOhan |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-30 : 02:25:53
|
quote:
Originally posted by mohan123
i have writen this query and it worked fine for me thanks for answering declare @length int = (select len(max(UserID)) from Users)update Users set LastName = 'LastName_' + right('0000000000' + convert(varchar(20), UserID), @length)P.V.P.MOhan
This will not give you what you asked foryou've ' around LastName so it will just append the string LastName_ followed by the sequence to every LastName valueie likeLastName_0001LastName_0002etc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
how to update a column with same sequence