Match rows of 2 Tables and Update

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

SQL Server 2005 Forums

Transact-SQL (2005)

Match rows of 2 Tables and Update

Author

Topic

kneekill
Yak Posting Veteran

76 Posts

Posted - 2009-10-06 : 06:41:23

Hi

I need help for updating a Table based on below scenario.

I have two tables A and B. I need to compare 'TextDescription' in TableA with 'NameDesc' of TableB and Update TableA with 'Bid' of TableB with the best possible matching row
based on 'Aid' and 'DescriptionID' present in both TableA and TableB

create table #TableA ( Aid int , DescriptionID int , TextDescription varchar(100),Bid int)
insert into #TableA (Aid,DescriptionID,TextDescription,Bid)
select 1,1,'John',null union all
select 1,2,'Rich',null union all
select 2,3,'Cliff',null union all
select 2,4,'Bill',null union all
select 2,5,'David',null

Select * from #TableA
-- DROP Table #TableA

create table #TableB ( Aid int , DescriptionID int , NameDesc varchar(100),Bid int)
insert into #TableB (Aid,DescriptionID,NameDesc,Bid)
select 1,1,'J',1 union all
select 1,1,'Jo',2 union all
select 1,1,'Joh',3 union all
select 1,2,'Ri',4 union all
select 1,2,'Ric',5 union all
select 1,2,'Rich',6 union all
select 2,3,'Cli',7 union all
select 2,3,'Ciff',8 union all
select 2,3,'Clif',9 union all
select 2,4,'Bll',10 union all
select 2,4,'Bill',11 union all
select 2,5,'Dav',12 union all
select 2,5,'Da',13

Select * from #TableB
-- DROP Table #TableB

My final result of TableA after the update should be as below:

Aid,DescriptionID,TextDescription,Bid
1, 1, 'John', 3
1, 2, 'Rich', 6
2, 3, 'Cliff', 9
2, 4, 'Bill', 11
2, 5, 'David', 12

Thanks

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-06 : 07:51:45

WITH CTE AS
(
Select
A.Aid,
A.DescriptionId,
A.TextDescription,
B.Bid,
CHARINDEX(B.NameDesc,A.TextDescription) AS MATCH,
LEN(B.NameDesc) MATCH_LEN
from
#TableA A,
#TableB B
Where
A.Aid=B.Aid AND
A.DescriptionID=B.DescriptionID
)
UPDATE #TableA
SET Bid=A.Bid
FROM
CTE A,
(
SELECT
Aid,
DescriptionId,
TextDescription,
MAX(MATCH_LEN)MATCH_LEN
FROM
CTE
WHERE
MATCH=1
GROUP BY
Aid,
DescriptionId,
TextDescription
)B
WHERE
A.Aid=B.Aid AND
A.DescriptionId=B.DescriptionId AND
A.TextDescription=B.TextDescription AND
A.MATCH_LEN=B.MATCH_LEN AND
A.MATCH=1 AND
A.Aid=#TableA.Aid AND
A.DescriptionId=#TableA.DescriptionId AND
A.TextDescription=#TableA.TextDescription

kneekill
Yak Posting Veteran

76 Posts

Posted - 2009-10-06 : 08:11:46

Thanks a lot sanoj, Every thing worked perfect except for below scenario.

I have one more question. If in Table A for the below row

select 2,5,'David XXX YYY ZZZ',null

If we have the following rows in Table B

select 2,5,'David',12
select 2,5,'David XXX YYY AAA',13

The update statement you have given me is returning 12 as 'Bid' instead of 13 ('David XXX YYY AAA' is more close match than 'David' as more letters are matching ).Can you please help me modify the query to achieve the result.

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-07 : 00:32:15

It will return 12 beacuse "David XXX YYY AAA" is not matching with "David XXX YYY ZZZ" ; Do you find any other logic to defferentiate these names? which one will you choose in the following names if
Table A contains
David XXX YYY AAA
and Table B contains
Davis XXX YYY AAA
David XXX YYY ZZZ
David XXX YYY

kneekill
Yak Posting Veteran

76 Posts

Posted - 2009-10-07 : 03:59:19

Hi sanoj

sorry for the late reply and also for confusing you

only criteria to find the best match for a TableA row in TableB is, Which is the 'NameDesc'that has most matching characters for the corresponding 'TextDescription ' from the begining (Start of the description)

so for the below scenario:

David XXX YYY AAA
and Table B contains
Davis XXX YYY AAA
David XXX YYY ZZZ

"David XXX YYY ZZZ" is the best match because the first 13 characters are matching with "David XXX YYY AAA" and it is the highest matched description from the starting in the group.

In "Davis XXX YYY AAA" only the first 4 characters are matching.

The scenario you have mentioned below will not happen with my requirement. both "David XXX YYY ZZZ" and "David XXX YYY" will not exist.Even if they exist since both have equal number of charchters matching we can pick any one of its 'Bid'.But as i said this case will not be my requirment.

Table A contains
David XXX YYY AAA
and Table B contains
Davis XXX YYY AAA
David XXX YYY ZZZ
David XXX YYY

please let me know if i am clear.Waiting for you response

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-07 : 04:21:32

Well. So the criterion for selecting the most appropriate text is based on the first X charecter matching. I think there is no such biult in functions available in SQL for this and you need to write a user defined function that traverse through the charecters of the string, but the query performance will be an issue in that case. What is the size of your tables A and B?

kneekill
Yak Posting Veteran

76 Posts

Posted - 2009-10-07 : 04:35:31

sanoj thanks for the reply,

Table A has 7 lakh rows and Table B has 20 lakh rows.But the idea is only execute the script on need basis for required "Aid".

for one "Aid", TableA may contain around 4 rows and TableB may contain around 20 rows.

can we use the same common table expression for traversing?.And also is possible to write a script to update All 7 lakh TableA rows, So that it will be only one time execution. Even if it takes around 5 - 10 minutes it would be fine.

quote:
Originally posted by sanoj_av

Well. So the criterion for selecting the most appropriate text is based on the first X charecter matching. I think there is no such biult in functions available in SQL for this and you need to write a user defined function that traverse through the charecters of the string, but the query performance will be an issue in that case. What is the size of your tables A and B?

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-07 : 05:12:34

Hi,
Try this...
/*
It will check for approximate match and not for full match and hence, It will return one id from TableB even if it doesnt have any match at all (in case it doesnt find any other records with better macth). If you dont want to return it like that way, add a condition to eliminate records where charindex=0
*/

WITH CTE AS
(
Select
A.Aid,
A.DescriptionId,
A.TextDescription,
B.Bid,
dbo.GetMatchingLength(A.TextDescription,B.NameDesc)AS MATCH_LEN
from
#TableA A,
#TableB B
Where
A.Aid=B.Aid AND
A.DescriptionID=B.DescriptionID
)
UPDATE
#TableA
SET
Bid=A.Bid
FROM
CTE A,
(
SELECT
Aid,
DescriptionId,
TextDescription,
MAX(MATCH_LEN)MATCH_LEN
FROM
CTE
GROUP BY
Aid,
DescriptionId,
TextDescription
)B
WHERE
A.Aid=B.Aid AND
A.DescriptionId=B.DescriptionId AND
A.TextDescription=B.TextDescription AND
A.MATCH_LEN=B.MATCH_LEN AND
A.Aid=#TableA.Aid AND
A.DescriptionId=#TableA.DescriptionId AND
A.TextDescription=#TableA.TextDescription

Create Function GetMatchingLength (@String1 varchar(1000),@String2 varchar(1000)) returns int
AS
BEGIN
Declare
@charLen int,
@charPos int

Set @charLen=Len(@String2)
Set @charPos=1

While (@charPos<=@charLen)
Begin
IF CHARINDEX(SUBSTRING(@String2,1,@charPos),@String1)=0
BREAK
Set @charPos=@charPos+1
End
Return @charPos-1
END

kneekill
Yak Posting Veteran

76 Posts

Posted - 2009-10-07 : 06:00:47

Hi sanoj,

Right now the function is checking for a match in every row present in TableB can we alter it to only do the check on respective "DescriptionID"s present in TableB?

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-07 : 06:09:49

No. It is not checking for every row in TableB.
Select
A.Aid,
A.DescriptionId,
A.TextDescription,
B.Bid,
dbo.GetMatchingLength(A.TextDescription,B.NameDesc)AS MATCH_LEN
from
#TableA A,
#TableB B
Where
A.Aid=B.Aid AND
A.DescriptionID=B.DescriptionID

The function will be called for every row of the recordset from the TableA and TableB where A.Aid=B.Aid AND
A.DescriptionID=B.DescriptionID. We cannot add more conditions to it as it do not serve our purpose..

kneekill
Yak Posting Veteran

76 Posts

Posted - 2009-10-07 : 06:23:16

sorry it was my mistake.I didn't check properly.

Thanks a ton sanoj ! you saved my day

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-10-12 : 09:04:46

update A
set A.Bid = B.bid
from #TableA A
join
(
Select DescriptionID,[bid]=MAX(Bid) from #TableB
group by DescriptionID
) B
on A.DescriptionID = B.DescriptionID

Iam a slow walker but i never walk back

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources