Didelis dėkui, Laimi, antru bandymu viskas prasisuko ok. Viskas
atrodo paprasta kai surašyta sintaksė :)

WITH table_Y(ID, Data)
AS
(
 SELECT ID, DateAdd("d", -1, Data)
 FROM table_X WHERE Result = 'Y'
)

UPDATE table_X T1
SET T1.Result =  'minus1'
FROM table_Y T2
WHERE T1.ID = T2.ID AND T1.Data = T2.Data