-- 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
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]