wiljago wiljago - 25 days ago 15
SQL Question

SQL query to match characters from N in X positions to characters from M in Y positions

I am trying to figure out how to match characters based on position, but I also want the sub string to match to be based on position.

Here is what I'm trying to do:

SELECT employee_table.LastFirst, employee_table.EmployeeId,
MondayPlgReport.created_by,
SUBSTR (employee_table.LastFirst, 0,5) AS employeeNameMatch
SUBSTR (MondayPlgReport.created_by, 2,4) AS userNameMatch
UPDATE MondayPlgReport
SET MondayPlgReport.created_by=employee_table.EmployeeId
WHERE employeeNameMatch=userNameMatch


I know that's wrong as hell, but hopefully it clarifies what I'm trying to achieve.

I also tried:

SELECT LastFirst, EmployeeId,
SUBSTR (LastFirst, 0,5) AS employeeNameMatch
FROM employee_table

SELECT created_by,
SUBSTR (created_by, 2,4) AS userNameMatch
FROM MondayPlgReport

UPDATE MondayPlgReport
SET MondayPlgReport.created_by=employee_table.EmployeeId
WHERE employeeNameMatch=userNameMatch


When I run either

SELECT LastFirst, EmployeeId,
SUBSTR (LastFirst, 0,5) AS employeeNameMatch
FROM employee_table


Or

SELECT created_by,
SUBSTR (created_by, 2,4) AS userNameMatch
FROM MondayPlgReport


Then I do see the query returns the results I'm seeking. The issue here is that the employees change frequently, and I am seeking another position. If the code needs to be updated every time someone leaves or is hired, then it will be useless to the department as soon as I leave.

Thanks very much for any help!

Answer

If you are using mysql you can use use JOIN this way

UPDATE MondayPlgReport
JOIN mployee_table
SET MondayPlgReport.created_by=employee_table.EmployeeId
ON  SUBSTR(employee_table.LastFirst, 0,5)=SUBSTR(MondayPlgReport.created_by, 2,4)

and for sqlite

UPDATE MondayPlgReport
SET created_by = (select  EmployeeId 
                  from  employee_table 
                  where  SUBSTR(LastFirst, 0,5) = SUBSTR(MondayPlgReport.created_by, 2,4));