Update with subselect and where

Question

The following code works fine in T-SQL, but not in JET SQL, in Access:

UPDATE Superliste_Temp 
SET [Plan-TGrp-Spanne_Stfl1] = 
(SELECT [Plan-TGrp-Spanne_Stfl1] 
 FROM Superliste_Temp
 INNER JOIN dbo_Common_preferences
     ON Superliste_Temp.Teil = dbo_Common_preferences.AktivesTeil)
WHERE [Teilegruppe] = 
(SELECT [Teilegruppe] 
 FROM Superliste_Temp
 INNER JOIN dbo_Common_preferences
     ON Superliste_Temp.Teil = dbo_Common_preferences.AktivesTeil);

Why does it not work!?

score 0 · Accepted Answer · answered Jun 11 '16 at 00:30

I have a hard time looking at that SQL to figure out what it's trying to do, but I know that Jet SQL needs the SET statement after the JOINS, so my best guess is:

UPDATE Superliste_Temp, Superliste_Temp AS ST1 
    INNER JOIN dbo_Common_preferences AS pref1 
    ON ST1.Teil = pref1.AktivesTeil 
SET Superliste_Temp.[Plan-TGrp-Spanne_Stfl1] = [ST1]![Plan-TGrp-Spanne_Stfl1]
WHERE (((Superliste_Temp.Teilegruppe)=[ST1]![Teilegruppe]));

Update with subselect and where

1 Answers1