Kažkas panašaus (sintaksės netikrinau):

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

ar

UPDATE T1
SET T1.Result =  'minus1'
FROM table_X T1
	LEFT JOIN (
		SELECT ID, DateAdd("d", -1, Data) AS ddata
		FROM table_X WHERE Result = 'Y'
	) AS T2
	ON T1.ID = T2.ID AND T1.Data = T2.ddata