SQLTeam.com | Weblogs | Forums

SQL Column update with comparison with another table

I upload data from a Txt File(Txt_Temp) where i have VinNumber with 6 digits. Another table name Resrve_Temp1 where i have Vinumber with 17 digit. now i need to update the vinnumber 6 digit to 17 digit or to new column in Txt_temp.

I tried this code with no succes and only one row is updating
update Txt_Temp Set Txt_Temp.Vinnumber=dbo.R_ResrvStock.Vin
from dbo.R_ResrvStock inner join Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=Txt_Temp.VinNumber
OR
Add this code in view
Select dbo.R_ResrvStock.Vin,R_Txt_Temp.Vinnumber,R_Txt_Temp.Model_Code
from dbo.R_ResrvStock inner join R_Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=R_Txt_Temp.VinNumber

Txt_Temp - Table
Vin
123456
123123
123789
Resrve_Temp1 - Table
asddfghjklk123654
asddfghjklk123456
asddfghjklk321564
asddfghjklk123123
asddfghjklk123789
asddfghjklk654655
asddfghjklk456465

My Result can be in Txt_Temp table or new table or with one or two columns
asddfghjklk123456 123456
asddfghjklk123123 123123
asddfghjklk123789 123789

can you try this,i am just assuming that there might be some empty space at the end of the string

update Txt_Temp Set Txt_Temp.Vinnumber=dbo.R_ResrvStock.Vin
from dbo.R_ResrvStock
inner join Txt_Temp
on ltrim(rtrim(Right (dbo.R_ResrvStock.Vin,6)))=ltrim(rtrim(Txt_Temp.VinNumber))

Thanks for the reply still I am getting only Last record.

If i manuly edit data in Txt_Temp table the result is fine