SQLTeam.com | Weblogs | Forums

SQL Column update with comparison with another table


#1

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


#2

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))


#3

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

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