?
Solved

Find the values of filed from one table searching through records in a second table

Posted on 2014-03-23
10
Medium Priority
?
195 Views
Last Modified: 2014-03-30
Hi Experts,

I have 2 tables.
Tb1 has ID1 and ID2 field , char (6) .
Tb2 has ID1 and Comment field.
I need to search through records of Tb2..comment  varchar (4000) and find values of ID2 and replace by ID1.


ID1 is numeric and ID2 is alphanumeric.

Example data:
tb1
---------
ID1     ID2
=============
999999  Oe123b
888888  e9874b

tb2
=================
ID1        Comment
234560  the entry is very similar to Oe123b. --->replace Oe123b with 999999
111111  See e9874b.                                        --->replace e9874b  with 888888


This code can’t even identify the entries that contain ID2.


DECLARE @ID2 CHAR(6)
--Set values for search  
set @ID2 = 'SELECT ID2 from tb1 where ID2 is not null'


SELECT DISTINCT a.ID1,a.Comment
FROM tb2  as a,
tb1 as b
WHERE a.Comment LIKE '% '+ @ID2 +'%'
AND b.ID2 is not null

Is there some way of using the replace function to update all instances.
Thank you.
0
Comment
Question by:PDF
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39950433
Try this:
UPDATE a SET
	Comment=REPLACE(Comment,b.ID2,b.ID1)
FROM 
	tb2  as a,
	inner join tb1 as b
		ON	a.Comment LIKE '% '+ b.ID2 +'%' 
		AND b.ID2 is not null

Open in new window

To check first you can run:
select
	a.ID1 as tb2ID,
	a.Comment as CommentBefore
	REPLACE(Comment,b.ID2,b.ID1) as CommentAfter,
	b.ID1,
	b.ID2
FROM 
	tb2  as a,
	inner join tb1 as b
		ON	a.Comment LIKE '% '+ b.ID2 +'%' 
		AND b.ID2 is not null

Open in new window

0
 

Author Comment

by:PDF
ID: 39950537
@ Zberteoc,

Thanks for the comment,
But , as I said my  query doesn’t do the job,
First I need to identify records where is update needed.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 1000 total points
ID: 39950622
OK, a couple of things to be careful with like char(6) with trailing spaces, or longer strings in the comment with a prefix matching the ID2 (e.g. '123456' will match with '12345678')

Having said that, the following works with the test scenario above...

-- first create some test tables

create table #TB1 (ID1 char(6), ID2 char(6))
create table #TB2 (ID1 char(6), Comment varchar(4000))

-- now insert our test data

insert #TB1 values ('999999', 'Oe123b')
insert #TB1 values ('888888', 'e9874b')

insert #TB2 values ('234560', 'the entry is very similar to Oe123b.') --->replace Oe123b with 999999
insert #TB2 values ('111111', 'See e9874b.')                          --->replace e9874b  with 888888
 
-- then run a test query

 select T2.*, T1.* 
 from #TB2 T2
 cross apply (select * from #TB1 T1 where charindex(T1.ID2, T2.Comment) > 0) T1

-- now do the update

 update T2 set comment = replace(comment,T1.id2,T1.id1)
 from #TB2 T2
 cross apply (select * from #TB1 T1 where charindex(T1.ID2, T2.Comment) > 0) T1

-- and finally, check the results

select * from #TB2

Open in new window

0
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 

Author Comment

by:PDF
ID: 39950674
Works beautifully on a test data.
Let me do on real tables.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39950730
@PDF

I don't think you understood my solution. The update query will do the job in 1 step without any loops. What is the problem with that?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39951042
Can confirm that Zberteoc's solution also works.

Small syntax error on the JOIN (remove the comma prior to the join, after the table alias 'a')
And a missing comma after the 'commentbefore' in the select.

Having just tested and fixed those small typo's, then I can confirm it does work as Zberteoc intended.

As for a loop, you might need one if there are multiple / different instances of ID2 within the comment.

For my code, as with Zberteoc's, you could encapsulate within a "while" loop using the select to test for a result / record count > 0

If that is the case, or a possibility, then the "while" loop would be worth considering.
0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 1000 total points
ID: 39951099
Thanks Mark,

Indeed that was a typo, actually I forgot to remove the comma after I modified the original query.  

As for your concern for the multiple tb2 ID2 occurrences it is only valid if there would be a rule saying that only the first or last occurrence of a matched value of ID1 would be preferred. In the case of UPDATE solution I gave, will still work but with one of the ID1 values that will be matched to a multiple ID2 value(could be the last one after joining).

The UPDATE, now corrected, will work perfectly:
UPDATE a SET
	Comment=REPLACE(Comment, b.ID2, b.ID1)
FROM 
	tb2  as a
	inner join tb1 as b
		ON	a.Comment LIKE '% '+ b.ID2 +'%' 
		AND b.ID2 is not null

Open in new window

And to check before update you use:
SELECT
	a.ID1 as tb2ID,
	a.Comment as CommentBefore,
	REPLACE(Comment,b.ID2,b.ID1) as CommentAfter,
	b.ID1,
	b.ID2
FROM 
	tb2  as a
	inner join tb1 as b
		ON	a.Comment LIKE '% '+ b.ID2 +'%' 
		AND b.ID2 is not null

Open in new window

0
 

Author Comment

by:PDF
ID: 39951115
Thank you @ Zberteoc & Mark !!!!

Now I have a dilemma how to fairly split points.
Any ideas or suggestions???
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39951128
I would be happy with sharing half and half...

But entirely up to you - it is your question :)
0
 

Author Closing Comment

by:PDF
ID: 39965495
Thank you @ Zberteoc & Mark !!!!
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question