Wacky JET syntax for UPDATE FROM

Today I’ve given a little help to a friend, with a JET 4.0 (Ms-ACCESS) thing.

Situation Given an existing schema:

Customers (Id, Name, …, Email) Ads (CustomerId, …)

the client wanted to add a field name TargetEmail to Ads table.

Adding the field was simple enough:

ALTER TABLE Ads ADD COLUMN TargetEmail TEXT(255)

Now the client wanted to initialise the TargetEmail field for existing ads, based on the Customer’s email.

A Naive and SQL Server jockey as I am, I gave him that little snippet:

UPDATE   Ads SET      Ads.TargetEmail = Customers.EmailFROM     Ads     JOIN Customers ON Ads.CustomerId = Customers.IdWHERE   Ads.TargetEmail IS NULL

Problem JET had refused that syntax.

Or rather, Jet is weird.

Solution Google to the rescue. answer was here.

UPDATE  Ads,         Customers SET     Ads.TargetEmail = Customers.EmailWHERE	Ads.CustomerId = Customers.Id  AND   Ads.TargetEmail IS NULL