Update with Subquery in MS ACCESS-CodePudding

I'm trying to make this query work but I'm getting errors:

    UPDATE SIRADATOK T1 INNER JOIN (SELECT sirid, MAX(SzolgaltatasokDatumig) AS MAXDATE
    FROM ADOK GROUP BY SIRID HAVING MAX(SzolgaltatasokDatumig)<>' ' AND
    MAX(SzolgaltatasokDatumig) IS NOT NULL)  AS T2 ON T1.SIRID=T2.sirid 
    SET MegvaltasIdeje = MAXDATE;

The error I get:

"Operation must use updatable query"

I tried this way too:

    UPDATE T2 
    SET T1.MegvaltasIdeje = T2.MAXDATE
    FROM SIRADATOK T1, (SELECT sirid, MAX(SzolgaltatasokDatumig) AS MAXDATE 
    FROM ADOK GROUP BY SIRID HAVING MAX(SzolgaltatasokDatumig)<>' ' 
    AND MAX(SzolgaltatasokDatumig) IS NOT NULL)  T2 
    WHERE  T1.SIRID=T2.sirid

but I get:

"Syntax error (missing operator) in query expression 'T2.MAXDATE FROM SIRADATOK T1'."

CodePudding user response：

using June7 comment, I created the following query that it works:

UPDATE 
  SIRADATOK AS T1 
SET 
  T1.MegvaltasIdeje = MID(
    DMAX(
      "SzolgaltatasokDatumig", "Adok", 
      "SzolgaltatasokDatumig<>' ' 
AND SzolgaltatasokDatumig IS NOT NULL AND SIRID=" & T1.SIRID
    ), 
    1, 
    10
  ) 
WHERE 
  DMAX(
    "SzolgaltatasokDatumig", "Adok", 
    "SzolgaltatasokDatumig<>' ' 
AND SzolgaltatasokDatumig IS NOT NULL AND SIRID=" & T1.SIRID
  ) Is Not Null 
  And DMAX(
    "SzolgaltatasokDatumig", "Adok", 
    "SzolgaltatasokDatumig<>' ' 
AND SzolgaltatasokDatumig IS NOT NULL AND SIRID=" & T1.SIRID
  )<> ' '

I added an extra MID for the SzolgaltatasokDatumig column, because the column is a DateTime and I need just the Date I know that is not the best optimized query, but this is just for a one-time use and It works.

thanks again, June7