CREATE TABLE dbo.IRBT 
(
	MOODYSRATE CHAR(10) NOT NULL,
	SNPRATE CHAR(10) NOT NULL,
	FITCHRATE CHAR(10) NOT NULL,
	IRBRATING NUMERIC(4) NOT NULL,
	CONSTRAINT IRBTPK PRIMARY KEY CLUSTERED (MOODYSRATE)
)

CREATE TABLE ACRT 
(
	CNO CHAR(100) NOT NULL,
	OPICSCNO CHAR(100) NULL,
	MOODYSRATE CHAR(10) NOT NULL,
	SNPRATE CHAR(10) NOT NULL,
	FITCHRATE CHAR(10) NOT NULL,
	MOODYSIRB NUMERIC(4) NULL,
	SPIRB NUMERIC(4) NULL,
	FITCHIRB NUMERIC(4) NULL,
	MOODYSWATCH BIT DEFAULT 0 NOT NULL,
	SPWATCH BIT DEFAULT 0 NOT NULL,
	FITCHWATCH BIT DEFAULT 0 NOT NULL,
	MIDRATEAGENCY CHAR(10) NULL,
	MIDRATING CHAR(10) NULL,
	MIDIRB NUMERIC NULL,
	NEGWATCH BIT DEFAULT 0 NOT NULL
)

INSERT INTO IRBT VALUES ('AAA','AAA','AAA','2')
INSERT INTO IRBT VALUES ('AA1','AA+','AA+','3')
INSERT INTO IRBT VALUES ('AA2','AA','AA','4')
INSERT INTO IRBT VALUES ('AA3','AA-','AA-','5')
INSERT INTO IRBT VALUES ('A1','A+','A+','6')
INSERT INTO IRBT VALUES ('A2','A','A','7')
INSERT INTO IRBT VALUES ('A3','A-','A-','8')
INSERT INTO IRBT VALUES ('BAA1','BBB+','BBB+','9')
INSERT INTO IRBT VALUES ('BAA2','BBB','BBB','10')
INSERT INTO IRBT VALUES ('BAA3','BBB-','BBB-','11')
INSERT INTO IRBT VALUES ('BA1','BB+','BB+','12')
INSERT INTO IRBT VALUES ('BA2','BB','BB','16')
INSERT INTO IRBT VALUES ('BA3','BB-','BB-','20')
INSERT INTO IRBT VALUES ('B1','B+','B+','22')
INSERT INTO IRBT VALUES ('B2','B','B','23')
INSERT INTO IRBT VALUES ('B3','B-','B-','24')
INSERT INTO IRBT VALUES ('CAA1','CCC+','CCC+','24')
INSERT INTO IRBT VALUES ('CAA2','CCC','CCC','24')
INSERT INTO IRBT VALUES ('CAA3','CCC-','CCC-','24')
INSERT INTO IRBT VALUES ('CA','CC','CC','24')
INSERT INTO IRBT VALUES ('C','C','C','24')
INSERT INTO IRBT VALUES ('D','D','D','25')

INSERT INTO ACRT VALUES ('ALLIANCE & LEICESTER BS','11292' ,'A2-', 'NR' ,'NR', '0.00','0.00','0.00','1','0','0','MOODYS','A2-','2.00','1')
INSERT INTO ACRT VALUES ('PRS 2005 2X A2A MBS (XS0234203684)','120577','AA2','A+' ,'AAA','4.00','6.00','2.00','0','0','0','MOODYS','AA2' ,'16.00','1')

--before update
select * from ACRT

;with cte as
(
	select *, ROW_NUMBER() over (Order by IRBRATING) as RowNum
	from IRBT
)

update ACRT set MIDIRB = c2.IRBRATING
from ACRT a
join cte c on a.MIDIRB = c.IRBRATING
join cte c2 on c.RowNum + 1 = c2.RowNum

--now we can view the data after the update
select * from ACRT

update ACRT set MIDIRB = min(IRBT2.IRBRATING)
from ACRT,IRBT,IRBT2 where ACRT.MIDIRB = IRBT.IRBRATING 
and IRBT2.IRBRATING > IRBT.IRBRATING

update ACRT set MIDIRB = min(IRBT2.IRBRATING)
from ACRT,IRBT,IRBT2 where ACRT.MIDIRB = IRBT.IRBRATING 
and IRBT2.IRBRATING > IRBT.IRBRATING

-- test this
SELECT 
	ACRT.*, 
	x.MIDIRB
FROM ACRT
INNER JOIN IRBT 
	ON IRBT.IRBRATING = ACRT.MIDIRB
CROSS APPLY (
	SELECT MIDIRB = MIN(IRBT2.IRBRATING) 
	FROM IRBT2 
	WHERE IRBT2.IRBRATING > IRBT.IRBRATING
) x 

-- if it correctly displays the rows to be updated and the correct value to update to,
-- then convert the SELECT to an UPDATE:
UPDATE ACRT SET MIDIRB = x.MIDIRB
FROM ACRT
INNER JOIN IRBT 
	ON IRBT.IRBRATING = ACRT.MIDIRB
CROSS APPLY (
	SELECT MIDIRB = MIN(IRBT2.IRBRATING) 
	FROM IRBT2 
	WHERE IRBT2.IRBRATING > IRBT.IRBRATING
) x