I’m forced to use MS Access for some legacy system. I don’t know what Microsoft thought when they made it so that MS Access uses a flavor of SQL that’s pretty different from their main product SQL Server. Did they just think nothing or is there some evil intent? Nothing probably. But anyhow, they did it.

In this post, I describe how to do a UPDATE with JOIN query in MS Access.

I want to replicate the following SQL Server query (in TSQL) in MS Access.

-- MSSQL
UPDATE Sachwert SET Wert = '1', DATE = GETDATE()
    FROM Artikel a
    INNER JOIN Sachwert AS s on a.GUID = s.GUID
    INNER JOIN Sgpos AS sgp on sgp.SACHGRUPPE = s.SACHGRUPPE 
    INNER JOIN Catalog c on c.Year = a.Year 
    WHERE a.Artikel = '104905' AND sgp.SACHGRUPPE = 'V1'

For MS Access you have to do these changes:

1. Put the name of the table where the update should happen before each name of each column which you want to change and remove the name of the table which is located before the SET. This gives
   UPDATE SET Sachwert.Wert='1', Sachwert.DATE = GETDATE().
2. Put the SET clause after the JOINs, remove the word FROM but leave the word UPDATE at the beginning.
3. If you have defined an alias, you must use this alias in the rest of the query. You then cannot longer use the original name. In our case, the SET clause must change to SET s.Wert='1', s.DATE = GETDATE() because of the Sachwert AS s.
4. If you have N JOINs, you have to add N-1 sets of parentheses. The left parentheses must all be directly after the UPDATE, the right ones one after each JOIN-clause – except the last one.
5. GETDATE() is called NOW() in Access.

So in the end you get this:

-- MS Access
UPDATE 
       ((Artikel a
          INNER JOIN Sachwert AS s on a.GUID = s.GUID)
          INNER JOIN Sgpos AS sgp on sgp.SACHGRUPPE = s.SACHGRUPPE)
          INNER JOIN Catalog c on c.Year = a.Year 
       SET s.Wert = '1', s.DATE = NOW()
       WHERE a.Artikel = '104905' AND sgp.SACHGRUPPE = 'V1'