CODEWITHSUNDEEP
sql-serverjoinmergeexists
G.KISA
G.KISA

Reputation: 95

MERGE between 2 tables, one table with 10million rows

TableA
match / Keyword
0 Stackoverflow
1 Youtube
1 Google
0 Yandex
1 Twitter
0 Facebook
0 Teacher
Totally 10million rows in TableA
There is Clustered index at Keyword column

TableB
match / word
1 You
1 Go
1 Twit
0 Home
0 Car
0 Pencil
0 Money
0 Weather
0 Her
Totally 500 rows in TableB
There is Clustered index at word column

My Question
i want to make a sql query to match every word from TableB if matches in TableA keywords. And update the TableB.match with 1
(TableA.keyword like '+TableB.word+'%') (will be matched)

NOT the middle of the keyword matches; (TableA.keyword like '%'+TableB.word+'%')
Forexample Her -> in Teacher (wont be matched)

I Tried to use MERGE

First Try;
i tried to match keywords with words and update TableB
i get error, because there is multiple matches in TableA and MERGE do not allow updating multiple times a row in Target table (TableB)

MERGE INTO [TableB] As XB 
USING (Select keyword FROM [TableA]) As XA 
ON XB.word LIKE ''+XA.keyword+'%' 
WHEN MATCHED THEN UPDATE SET XB.match=1;

Second Try;
i tried to match words with keywords and update TableA
i get what i want, The problem is, it takes 1 hour to execute the query for 500words in 10million keywords.

MERGE INTO [TableA] As XA 
USING (Select word FROM [TableB]) As XB 
ON XB.word LIKE ''+XA.keyword+'%' 
WHEN MATCHED THEN UPDATE SET XA.match=1;

Is there an option to fasten these lookups in SecondTry?

Upvotes: 1

Views: 103

Answers (1)

DForck42
DForck42

Reputation: 20367

An update statement will suffice for what you're trying to do. Note that this will probably not perform very well as SQL isn't great at comparing strings. 

declare @a table (match int, keyword varchar(50))
declare @b table (match int, keyword varchar(50))

insert into @a values (0, 'Stackoverflow')
insert into @a values (0, 'Youtube')
insert into @a values (0, 'Google')
insert into @a values (0, 'Yandex')
insert into @a values (0, 'Twitter')
insert into @a values (0, 'Facebook')
insert into @a values (0, 'Teacher')


insert into @b values (0, 'You')
insert into @b values (0, 'Go')
insert into @b values (0, 'Twit')
insert into @b values (0, 'Home')
insert into @b values (0, 'Car')
insert into @b values (0, 'Pencil')
insert into @b values (0, 'Money')
insert into @b values (0, 'Weather')
insert into @b values (0, 'Her')

--commented out because user didn't want this, but it matches the provided data
--update @a
--set match = 1
--where keyword in
--(
--  select 
--      distinct a.keyword
--  from @a a
--  cross apply @b b
--  where a.keyword like b.keyword + '%'
--)

update @b
set match = 1
where keyword in
(
    select 
        distinct b.keyword
    from @a a
    cross apply @b b
    where a.keyword like b.keyword + '%'
)

select *
from @a

select *
from @b

--EDIT BY Sean-- Here is how you could do this as a correlated subquery so you can use EXISTS.

update b
set match = 1
from @b b
where exists
(
    select b.keyword
    from @a a
    where a.keyword like b.keyword + '%'
)

Upvotes: 2

Related Questions