SQLTeam.com | Weblogs | Forums

Update table based on matching 2 tables



I have 2 tables. FullName and InputName. FullName has a primary key column Id and Name column. InputName has no index and consists of a InName column only. I want to update the data in the InputName table based on the values on the FullName table.

So, in the InputName table there might be Tony D Jones, in the FullName table Tony D Jones is shown as T D Jones. I want to update the InputName table to match that in the FullName table. Where a sufficiently close match is not found, no action is taken. How would I do that please.

The discrepancy between the names in the tables arises because I am scraping the data from different sources from the web, all of which record the same people in slightly different ways and I need to make regular the names in the dbase.

Thanks for all and any help.


This might get you some of the way:

with fullname(id,[name])
  as (          select 1,'Tony D Jones'
      union all select 2,'Tina D Jones'
      union all select 3,'Indiana Jones'
  as (          select 'T D Jones'
      union all select 'I Jones'
  as (select [name]
            ,charindex(' ',[name]+' ')
        from fullname
       group by [name]
      union all
      select [name]
            ,charindex(' ',[name]+' ',i2+1)
        from fullname_indexes
       where charindex(' ',[name]+' ',i2+1)>0
  as (select [name]
            ,row_number() over(partition by [name] order by i1 desc)
        from fullname_indexes
  as (select [name]
            ,stuff((select ' '
                          +case when b.rn=1 then b.namepart else substring(b.namepart,1,1) end
                      from fullname_splitparts as b
                     where b.[name]=a.[name]
                     order by b.rn desc
                       for xml path('')
        from fullname_splitparts as a
       group by [name]
select a.inname
      ,count(a.inname) over(partition by a.inname) as matches
  from inputname as a
       left outer join fullname_parts as b
                    on b.nameparts=a.inname


Thank you so much for taking the time and trouble to work out a solution for me. I didn't fully realise what I was asking. Thank you again.