select MIDIRB, 

IRBT.ID as IRBT_ID,

IRBT.IRBRATING as IRBT_IRBRATING,

ACRT.NEGWATCH as ACRT_NEGWATCH,

case when ACRT.NEGWATCH = 1 then (SELECT IRBT.IRBRATING FROM IRBT I WHERE I.ID = (IRBT.ID +1)) ELSE IRBT.IRBRATING end,

*

FROM ACRT

 inner join IRBT

on IRBT.MOODYSRATE = ACRT.MOODYSRATE

-- try this
select 
	MIDIRB, 
	IRBT.ID as IRBT_ID,
	IRBT.IRBRATING as IRBT_IRBRATING,
	ACRT.NEGWATCH as ACRT_NEGWATCH,
	Newcolumn = case when ACRT.NEGWATCH = 1 then x.IRBRATING ELSE IRBT.IRBRATING end,

	*

FROM ACRT
INNER JOIN IRBT
	ON IRBT.MOODYSRATE = ACRT.MOODYSRATE
CROSS APPLY (
	SELECT i.IRBRATING 
	FROM IRBT i 
	WHERE i.ID = IRBT.ID +1
) x

-- The UPDATE equivalent would look like this
UPDATE ACRT 
	SET Yourcolumn = CASE 
		WHEN ACRT.NEGWATCH = 1 THEN x.IRBRATING 
		ELSE IRBT.IRBRATING END
FROM ACRT
INNER JOIN IRBT
	ON IRBT.MOODYSRATE = ACRT.MOODYSRATE
CROSS APPLY (
	SELECT i.IRBRATING 
	FROM IRBT i 
	WHERE i.ID = IRBT.ID +1
) x