MS Access 2016 UPDATE query with ALIKE

Question

I'm trying to run the following UPDATE query in MS Access 2016 VBA:

UPDATE M_Subscriber
    SET M_Subscriber.RatingID = t.RatingID
    FROM
        ( SELECT M_Subscriber.ID AS SubscriberID, E_SubscriberRating.ID AS RatingID
              FROM M_Subscriber, E_SubscriberRating
              WHERE M_Subscriber.Labels ALIKE '%' & E_SubscriberRating.Description & '%'
        ) AS t
    WHERE M_Subscriber.ID = t.SubscriberID

However, when trying to execute it I get a "syntax error (missing operator) in query expression" error.

I know that the SELECT part of the query by itself works just as expected (though using LIKE instead of ALIKE does not, even when using * for the wildcard...).

I've also tried this version of the query:

UPDATE M_Subscriber
    SET M_Subscriber.RatingID = E_SubscriberRating.ID
    FROM M_Subscriber
        INNER JOIN E_SubscriberRating
        ON M_Subscriber.Labels ALIKE '%' & E_SubscriberRating.Description & '%'

But again the same error message (and again, a standalone SELECT statement using the INNER JOIN works just fine).

Some info on what I'm trying to do:

I have a field of labels (just at string of various words) for my subscribers (in M_Subscriber) and one of those words can be a rating. On the other hand I have a table (E_SubscriberRatings) of all possible words for ratings (E_SubscriberRating.Description). I'm now trying to set the M_Subscriber.RatingID field based on the ID of the SubscriberRating whose description shows up in the M_Subscriber.Labels field (if it actually contains any such description).

At this point, I've really run out of ideas what to tweak to figure out what I'm doing wrong. I've found various examples that join by column = column but none that join using LIKE.

Parfait Parfait · Accepted Answer · 2017-12-09T05:25:09

MS Access does not use FROM in UPDATE queries unlike other SQL dialects. Consider UPDATE...INNER JOIN (a method it shares with MySQL) and do so with table aliases:

UPDATE M_Subscriber m 
INNER JOIN E_SubscriberRating e
ON m.Labels ALIKE '%' & e.Description & '%'
SET m.RatingID = e.ID;

Alternatively, with WHERE clause:

UPDATE M_Subscriber m, E_SubscriberRating e
SET m.RatingID = e.ID
WHERE m.Labels ALIKE '%' & e.Description & '%';

MS Access 2016 UPDATE query with ALIKE

2 Answers