Update Column based on section of identifier

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update Column based on section of identifier

Author

Topic

Lionheart
Starting Member

41 Posts

Posted - 2010-06-23 : 13:34:16

I have 2 tables: T1 and T2.

T1 has two columns; the first is a 3 digit code (but stored as varchar) and the second is null (set as varchar). I need to keep both of these as varchar.

T2 has two columns; the first is a 2 digit code (stored as an int) and the second is a Name to match the identifier in column 1.

What I am trying to do is update T1.col2 with the name in T2.col2 where right(T1.col1,2)=T2.col1.

The code I have is like this

Update T1
Set T1.col2 = T2.col2
From  T1 inner join
T2 on T2.col1 = convert(int, right(T1.col1,2))

but this does not do anything. Can anyone offer any suggestions please?

Thx

LH

Sachin.Nand

2937 Posts

Posted - 2010-06-23 : 14:03:57

I dont see anything wrong with the syntax.
Just convert your update query to a select query & check the values of convert(int, right(T1.col1,2)) and T2.col1 and see whether they are matching.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Lionheart
Starting Member

41 Posts

Posted - 2010-06-24 : 12:37:53

Okay, got it sorted...and am posting just in case anyone else has this issue.

After frustration I realized that I actually needed

Update T1
Set T1.col2 = T2.col2
From  T1 inner join
T2 on T2.col1 = convert(int, right(left(T1.col1,3),2))

As a varchar of greater than 3 originally, by just doing the right 2 it was pulling 2 blanks so getting nothing. Now it works fine...annoying...yes...but it works!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources