SQL Column update with comparison with another table

Question

SQL Column update with comparison with another table

syed_3177

SSC Enthusiast

Points: 137
More actions
November 10, 2015 at 3:18 am

#302173

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
no success
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

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Your code looks ok. What datatype is dbo.R_ResrvStock.Vin?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sgmunson SSC Guru Points: 110640 More actions · Answer 2

ChrisM@Work (11/10/2015)
Your code looks ok. What datatype is dbo.R_ResrvStock.Vin?

With that thought in mind, perhaps something like: LTRIM(RTRIM(field1)) = RIGHT(field2, 6) ?

e.g.

UPDATE TT

SET TT.Vinnumber = RRS.Vin

FROM dbo.R_ResrvStock AS RRS

INNER JOIN Txt_Temp AS TT

ON RIGHT(RRS.Vin, 6) = LTRIM(RTRIM(TT.VinNumber));

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Probably this will return the expected rows?

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 (RTRIM(dbo.R_ResrvStock.Vin),LEN(R_Txt_Temp.VinNumber))=R_Txt_Temp.VinNumber

Without DDL and Sample data as insert statements, we're only guessing.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2