T-Sql Contruction
-
I have TableA and TableB below. What I want to do is to take fields of TableB insert them into TableA where TableB.Reference = TableA.trad_type without -A's. If you can have a look, they are equal if you remove the -A's. Please help me to construct the T-SQL. I will be replacing the TableA.reference with TableB.ContId, TableA.principal with TableB.Principal, TableA.book with TableB.book and TableA.strategy with TableB.strategy
TableA
trad_type reference principal book strategy
SL9975-A-A NULL NULL NULL NULL
SL9887-A NULL NULL NULL NULL
TableB
ContId reference principal book strategy
11111 SL9975 LOAN UNALLOCATED UNALLOCATED
22222 SL9887 REFUND ALLOCATED UNALLOCATED
-
how about
--update A
--set reference = B.ContId
--, principal = B.Principal
--, book = B.book
--, strategy = B.strategy
select *
from TableA A
, TableB B
where B.reference = substring(A.trad_type,1,6)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Hi,
Could try this as welll if column width is not fixed
update TA
set reference =b.ContId, principal =b.principal,Book =b.Book, Strategy=b.Strategy
from TA a, TB b
where replace(a.trad_type,'-A','')=b.reference
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me -
I cannot use this
select *from TableA A
, TableB B
where B.reference = substring(A.trad_type,1,6)
because the reference could be SL997599 or SL9975586666 so substring won't work
-
couldn't deduct that from the example
in that case , Helen 's response will work.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
use somthing like this select REPLACE( 'SL9975-A-A' , '-A' , '' )
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply