Just to help you with future posts, if you post your data in a nice and clean consumable format it will go a long way.

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')

The above code will actually run on SQL Server and doesn't have the SYBASE LOCK ALLPAGES. 😉

OK so now on to a solution for your issue.

Using a cte this is actually pretty simple.

--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