SQL update with inner join on 2 additional tables

Question

I am struggling with the following query on ms-access :

UPDATE
(
    (
    DWH_TICKERS_LIST
    INNER JOIN  ODS_PAGE_HEADER_INFO ON ODS_PAGE_HEADER_INFO.TICKER_CODE = DWH_TICKERS_LIST.TICKERS_LIST__TICKER
    ) 
INNER JOIN SECTOR ON SECTOR.NAME = DWH_TICKERS_LIST.TICKERS_LIST__NAME
)
SET 
    DWH_TICKERS_LIST.TICKERS_LIST__ISIN_CODE             = ODS_PAGE_HEADER_INFO.ISIN_CODE
,   DWH_TICKERS_LIST.TICKERS_LIST__CURR                  = ODS_PAGE_HEADER_INFO.CURR
,   DWH_TICKERS_LIST.TICKERS_LIST__SECTOR_FK             = SECTOR.SECTOR_PK
,   DWH_TICKERS_LIST.TICKERS_LIST__TOTALVOLUME           = ODS_PAGE_HEADER_INFO.TOTALVOLUME
,   DWH_TICKERS_LIST.TICKERS_LIST__ELIGIBILITY_SRD       = ODS_PAGE_HEADER_INFO.ELIGIBILITY_SRD
,   DWH_TICKERS_LIST.TICKERS_LIST__ELIGIBILITY_PEA       = ODS_PAGE_HEADER_INFO.ELIGIBILITY_PEA
,   DWH_TICKERS_LIST.TICKERS_LIST__ELIGIBILITY_22H       = ODS_PAGE_HEADER_INFO.ELIGIBILITY_22H
,   DWH_TICKERS_LIST.DWH_TICKERS_LIST__UPDATE_DATETIME   = Now()

I got inspired from that one. However I get that error :

sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('07002', '[07002] [Microsoft][Pilote ODBC Microsoft Access] Trop peu de paramètres. 1 attendu. (-3010) (SQLExecDirectW)')

i.e. too few parameters, 1 expected.

Any idea how to correct this?

Answer 1

Name is a reserved word in some contexts, although it is not listed in the Access SQL reserved words.

Nevertheless try enclosing it in brackets: SECTOR.[NAME]

The outer parentheses surrounding the JOIN list are not needed (but don't hurt probably).

If [NAME] doesn't help, the most probable cause is that you have a typo somewhere in your field names.

You can improve readability by using short aliases for table names:

UPDATE
    (
    DWH_TICKERS_LIST AS tl
    INNER JOIN  ODS_PAGE_HEADER_INFO AS hi 
      ON hi.TICKER_CODE = tl.TICKERS_LIST__TICKER
    ) 
    INNER JOIN SECTOR AS s ON s.[NAME] = tl.TICKERS_LIST__NAME

SET 
    tl.TICKERS_LIST__ISIN_CODE             = hi.ISIN_CODE
,   tl.TICKERS_LIST__CURR                  = hi.CURR
,   tl.TICKERS_LIST__SECTOR_FK             = s.SECTOR_PK
etc.