UPDATE from another table using LIKE between target and source columns to match rows

Question

I have 2 tables, tblSource and tblTarget

tblSource:

ID	Info	Born	City
1	1954 Barry Nelson TV series	US	San Francisco
2	1954 Sean Connery Dr. No	UK	Edinburgh
3	1967 David Niven Casino Royale	UK	London
4	1967 George Lazenby On Her Majesty´s Secret Service	AU	New South Wales
5	1969 Roger Moore Moonraker	UK	London
6	1973 Timothy Dalton The Living Daylights	UK	Wales
7	1987 Pierce Brosnan The World Is Not Enough	IE	County Louth

tblTarget:

ID	Name	Born	City
10	Sean Connery
11	Roger Moore
12	Timothy Dalton
13	Pierce Brosnan
14	Daniel Craig

Goal is to update Born and City columns in tblTarget by matching rows with LIKE from tblSource. On a row without a match, update must not happen. Tables are not huge so a hardcoded list like below can be created from tblTarget and used in SQL:

('%Sean Connery%',
 '%Roger Moore%',
 '%Timothy Dalton%',
 '%Pierce Brosnan%',
 '%Daniel Craig%')

Sure, a fully dynamic solution would be ideal for future needs

The result should be,

tblTarget:

ID	Name	Born	City
10	Sean Connery	UK	Edinburgh
11	Roger Moore	UK	London
12	Timothy Dalton	UK	Wales
13	Pierce Brosnan	IE	County Louth
14	Daniel Craig

I'm this far but don't know how to write LIKE condition properly

UPDATE t
SET t.Born = s.Born,
    t.City = s.City
FROM tblTarget t
JOIN tblSource s
ON t.Name LIKE s.Info
WHERE s.Info IN 
('%Sean Connery%',
 '%Roger Moore%',
 '%Timothy Dalton%',
 '%Pierce Brosnan%',
 '%Daniel Craig%')

Any help highly appreciated

Answer 1

Having the same data duplicated in multiple tables is probably a design flaw, however you can join using a like match.

On the assumption your source data does not contain duplicates you can simply join the tables and use an updatable table expression;

with updateme as (
    select t.*, s.born sBorn, s.City sCity
    from target t
    join source s on s.info like Concat('%', t.name, '%')
)
update updateme set born = SBorn, city = sCity;

if your source table contains multiple rows per Name you could move the join to a cross apply and select 1 row only - again presumably your source table would be duplicating the same data per Name.

with updateme as (
    select t.*, s.sBorn, s.sCity
    from target t
    cross apply (
        select top(1) s.born sBorn, s.City sCity
        from source s where s.info like Concat('%', t.name, '%')
    )s
)
update updateme set born = SBorn, city = sCity;