How do I run an SQL update query using a like statement

Question

I am trying to update a field in a table using an SQL update query where there is a like statement referencing a value in another table. They syntax unfortunately is not working. Below is my code. In short, I am trying to put a '1' in the field 'Query07ParolaChiave' in the table 'tblSearchEngine01' when the value located in table 'tblsearchengine07' is present in the field 'tblMasterListOfEventsNotes' located in the table 'tblSearchEngine01'. I think my code is almost complete but there is a syntax issue which i cant find.

st_sql = "UPDATE tblSearchEngine01, tblSearchEngine07 SET tblSearchEngine01.Query07ParolaChiaveSelect = '1' WHERE ((([tblSearchEngine01].[tblMasterListOfEventsNotes]) Like " * " & [tblsearchengine07].[ParolaChiave] & " * "))"
    Application.DoCmd.RunSQL (st_sql)

Answer 1

I suggest you 2 solutions :

This one is using EXISTS functions, and will check for each row in tblSearchEngine01 if there is a matching value in tblsearchengine07

UPDATE 
    tblSearchEngine01
SET 
    tblSearchEngine01.Query07ParolaChiaveSelect = '1' 
WHERE 
    EXISTS (SELECT 1 
            FROM tblsearchengine07
            WHERE [tblSearchEngine01].[tblMasterListOfEventsNotes] Like '*' & [tblsearchengine07].[ParolaChiave] & '*')

This one is more performant because it uses JOIN

UPDATE 
    tblSearchEngine01 
    INNER JOIN tblsearchengine07 
        ON [tblSearchEngine01].[tblMasterListOfEventsNotes] Like '*' & [tblsearchengine07].[ParolaChiave] & '*'
SET 
    tblSearchEngine01.Query07ParolaChiaveSelect = '1'

I read something like in ADO/VBA, you have to use % instead of * as the wildcard.

You can have more information on wildcard and LIKE comparator here

UPDATE

Why the '1' after select in your first solution? EXISTS (SELECT 1 ... is better for performance because it return only the number 1 instead of fields, anyway EXISTS just stop the excecution after 1 element found.

'Performant' means more consuming in regards to space and memory? JOIN is more performant in term of time of execution, RDBMS are far better at joining tables than using subquery, in some rare case, it's more interesting to use the 1st solution.

Also, any initial thoughts as to why my original solution (coming straight from an Access Query which works) does not function? I cannot really know but perhaps it's because of " * ", because you are saying SPACE + * + SPACE + VALUE + SPACE + * + SPACE. For ex : 'John' LIKE ' John '

May be with "*" instead of " * " could solve it...

I have no other track, I'm not Access sql developper, I usually play around Sql server/Oracle/mySql, hope it helped. ;)