CODEWITHSUNDEEP
t-sqlsql-server-2005
Paul Exter
Paul Exter

Reputation: 85

Update query using cross join

I have a table that's a dictionary of words you might find in a company name, and their synonyms. I want to use this to standardize the names in another table. For example, "WIDGET COMPANY INCORPORATED" would be standardized to "WIDGET CO INC". I thought it would be as simple as the below cross join, but it seems to only run for the first synonym in the list, rather than the entire list. Am I going about this the wrong way? I was going to try to do it in SSIS instead if I can't figure this out, but I think that would be over-complicating what should be a relatively simple task... of course, I realize since this is a cross join, we're talking about a lot of rows - 50 million, to be precise...

update [dbo].[Company addresses]
set [Name 1 syn] = replace(a.[Name 1 syn], ' ' + b.[Synonym] + ' ', ' ' + b.[Base] + ' ')
from [dbo].[Company addresses] a
cross join dbo.Synonyms b

Upvotes: 2

Views: 4393

Answers (1)

GilM
GilM

Reputation: 3771

How about something like:

DECLARE @RC int = 1;

WHILE @RC > 0
BEGIN
  update [dbo].[Company addresses]
  set [Name 1 syn] = replace(a.[Name 1 syn], ' ' + b.[Synonym] + ' ', ' ' + b.[Base] + ' ')
  from [dbo].[Company addresses] a
  inner join dbo.Synonyms b on a.[Name 1 syn] LIKE '% ' + b.[Synonym] + ' %';

  SET @RC = @@ROWCOUNT;
END

Upvotes: 3

Related Questions