MySql Searching two tables and comparing columns with like and %

I am trying to select data from two tables and insert it into another one. I want to select the data when certain things match in each table. The problem is that one of the comparisons needs to be a like and I am at a loss. The sql statement is below and I believe that it will at least show what I am trying to do.

insert into hr_numbers 
  (`user_id`, `hr_number`) 
  select distinct u.user_id, t.开发者_Go百科num 
    from temp t, users u 
   where t.name like '%' + u.l_name + '%';

I have tried to use procedures but cannot figure that out or even tell if that would help.

First, make sure the SELECT is returning what you want before attempting insertion. This:

SELECT DISTINCT 
       u.user_id, 
       t.num 
  FROM temp t, users u 
 WHERE t.name like '%' + u.l_name + '%'

...is using ANSI-89 JOIN syntax. ANSI-92 join syntax is a better choice:

SELECT DISTINCT
       u.user_id,
       t.num
  FROM USERS u
  JOIN TEMP t ON t.name LIKE CONCAT('%', u.l_name ,'%')

...but the join criteria looks flawed. It's going to link rows were the last name appears in the t.name value, leading to the Smith problem: If the last name is "Smith", all rows will link up - even if the names are John, Jim, etc.

Try this:

INSERT INTO hr_numbers(`user_id`, `hr_number`)
SELECT DISTINCT U.user_id, T.num
FROM temp T
INNER JOIN users U ON T.NAME LIKE '%' + U.l_name + '%'