InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 21:52:52 GMT20http://www.sqlservercentral.com/Forums/Topic1369060-391-1.aspx[quote][b]ronan.healy (10/9/2012)[/b][hr]still getting this errorMsg 208, Level 16, State 1, Line 1Invalid object name 'IRBT2'.[/quote]Is that a table? In your original post you did not mention that table.And fwiw you should use the newer join syntax instead of the old style joins.[code]update ACRT set MIDIRB = min(IRBT2.IRBRATING)from ACRTjoin IRBT on ACRT.MIDIRB = IRBT.IRBRATING join IRBT2 on IRBT2.IRBRATING > IRBT.IRBRATING[/code]This imho is easier to read and is less prone to coding error. With the comma separated list it is very easy to forget a join condition and you end up with a cross join.Tue, 09 Oct 2012 07:26:35 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1369060-391-1.aspxstill getting this errorMsg 208, Level 16, State 1, Line 1Invalid object name 'IRBT2'.Tue, 09 Oct 2012 04:46:58 GMTronan.healyhttp://www.sqlservercentral.com/Forums/Topic1369060-391-1.aspxthanks for response will try thatTue, 09 Oct 2012 04:45:24 GMTronan.healyhttp://www.sqlservercentral.com/Forums/Topic1369060-391-1.aspx[quote][b]ronan.healy (10/9/2012)[/b][hr]hiout of interest is it possible to do it this way[code="sql"]update ACRT set MIDIRB = min(IRBT2.IRBRATING)from ACRT,IRBT,IRBT2 where ACRT.MIDIRB = IRBT.IRBRATING and IRBT2.IRBRATING > IRBT.IRBRATING[/code]and if so what do i have to do to get this error out of itMsg 208, Level 16, State 1, Line 1Invalid object name 'IRBT2'.IRBT2 is a table in database[/quote]No - there's an aggregate operator but no GROUP BY clause.[code="sql"]-- test thisSELECT ACRT.*, x.MIDIRBFROM ACRTINNER JOIN IRBT ON IRBT.IRBRATING = ACRT.MIDIRBCROSS 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.MIDIRBFROM ACRTINNER JOIN IRBT ON IRBT.IRBRATING = ACRT.MIDIRBCROSS APPLY ( SELECT MIDIRB = MIN(IRBT2.IRBRATING) FROM IRBT2 WHERE IRBT2.IRBRATING > IRBT.IRBRATING) x [/code]Tue, 09 Oct 2012 04:41:06 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1369060-391-1.aspxhiout of interest is it possible to do it this way[code="sql"]update ACRT set MIDIRB = min(IRBT2.IRBRATING)from ACRT,IRBT,IRBT2 where ACRT.MIDIRB = IRBT.IRBRATING and IRBT2.IRBRATING > IRBT.IRBRATING[/code]and if so what do i have to do to get this error out of itMsg 208, Level 16, State 1, Line 1Invalid object name 'IRBT2'.IRBT2 is a table in databaseTue, 09 Oct 2012 04:23:19 GMTronan.healyhttp://www.sqlservercentral.com/Forums/Topic1369060-391-1.aspx[quote][b]ronan.healy (10/5/2012)[/b][hr]thanks very much for help will try thatand also will try improve my [post in futureagain thanks[/quote]No problem. Happy to help. The best thing you can do is to test your ddl and dml so you know it will work. :-D Let me know if that doesn't work.Fri, 05 Oct 2012 09:48:30 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1369060-391-1.aspxthanks very much for help will try thatand also will try improve my [post in futureagain thanksFri, 05 Oct 2012 09:46:48 GMTronan.healyhttp://www.sqlservercentral.com/Forums/Topic1369060-391-1.aspxJust to help you with future posts, if you post your data in a nice and clean consumable format it will go a long way.[code]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')[/code]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.[code]--before updateselect * from ACRT;with cte as( select *, ROW_NUMBER() over (Order by IRBRATING) as RowNum from IRBT)update ACRT set MIDIRB = c2.IRBRATINGfrom ACRT ajoin cte c on a.MIDIRB = c.IRBRATINGjoin cte c2 on c.RowNum + 1 = c2.RowNum--now we can view the data after the updateselect * from ACRT[/code]Fri, 05 Oct 2012 09:41:20 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1369060-391-1.aspxya just double checked there and its ordered on the irbratingFri, 05 Oct 2012 09:22:38 GMTronan.healyhttp://www.sqlservercentral.com/Forums/Topic1369060-391-1.aspx[quote][b]ronan.healy (10/5/2012)[/b][hr]hii think i just put them in to quick to get a reply.they are odrered on the number from the irbt table.so 2- 25 in that oder2345 etc[/quote]So you are saying that we can use IRBRATING as the order?Fri, 05 Oct 2012 09:16:05 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1369060-391-1.aspxhii think i just put them in to quick to get a reply.they are odrered on the number from the irbt table.so 2- 25 in that oder2345 etcFri, 05 Oct 2012 09:13:33 GMTronan.healyhttp://www.sqlservercentral.com/Forums/Topic1369060-391-1.aspx[quote][b]ronan.healy (10/5/2012)[/b][hr]well there will be alot more rows in my table some will have negwatch some wont so what i need to do is update the MIDIRB rows in the acrt table with the next highest value to it if on neg watch.i get the values from the irbt tableso if its 2 next is 3id its 3 next is 4 if its 16 next is 20[/quote]I think I understand what you are after. The challenge is you need some way to order IRBT. The concept of next does not exist until you specify an order. Assuming you can figure out what column to use as an order by this is pretty simple.Fri, 05 Oct 2012 09:07:44 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1369060-391-1.aspxwell there will be alot more rows in my table some will have negwatch some wont so what i need to do is update the MIDIRB rows in the acrt table with the next highest value to it if on neg watch.i get the values from the irbt tableso if its 2 next is 3id its 3 next is 4 if its 16 next is 20Fri, 05 Oct 2012 09:03:14 GMTronan.healyhttp://www.sqlservercentral.com/Forums/Topic1369060-391-1.aspxNo problem.So the ddl for ACRT should look like this?[code]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)[/code]I need some explanation as to what you are trying to do.[quote]so what im trying to do is if in the acrt table or table 2 if the NEGWATCH =1 then for MIDIRB in the acrt table or table 2 i want to add the next value from irbt or table 1.sosay negwatch =1 and midird before this update is 16 then when update is done and from the irtb table or table 1 the midirb = 20 as the next number in the irbt table after 16 is 20hope this is better explained but not so sure[/quote]Do you need to update ACRT? Both rows in ACRT have NEGWATCH = 1 so both rows should be updated? What is the logic for the update?Fri, 05 Oct 2012 08:59:49 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1369060-391-1.aspxya this is the acrt table now as i have it in our system and negwatch is last columnprobably missing the last braket didnt copy and past the lot thought i had sorry[code="sql"]CREATE TABLE dbo.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,)[/code]Fri, 05 Oct 2012 08:54:08 GMTronan.healyhttp://www.sqlservercentral.com/Forums/Topic1369060-391-1.aspx[quote][b]ronan.healy (10/5/2012)[/b][hr]sorry put in the older version of table[code="sql"]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,[/code][/quote]This is incomplete. Is this the table ACRT now? Is NEGWATCH the last column?Fri, 05 Oct 2012 08:51:46 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1369060-391-1.aspxsorry put in the older version of table[code="sql"]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,[/code]Fri, 05 Oct 2012 08:50:13 GMTronan.healyhttp://www.sqlservercentral.com/Forums/Topic1369060-391-1.aspxWell the inserts have incorrect string delimiters for sql. When I changed the " to ' I can't insert to ACRT, the insert doesn't match the table definition.[quote]so what im trying to do is if in the acrt table or table 2 if the NEGWATCH =1 then for MIDIRB in the acrt table or table 2 i want to add the next value from irbt or table 1.sosay negwatch =1 and midird before this update is 16 then when update is done and from the irtb table or table 1 the midirb = 20 as the next number in the irbt table after 16 is 20hope this is better explained but not so sure[/quote]There is no column MIDIRB. There is no column midird.Tables in sql are unordered collections of information. There is no concept of "next". I am willing to help but I don't even know where to begin.Fri, 05 Oct 2012 08:46:37 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1369060-391-1.aspxhi thanks for responseok will try post it betterhe is the two tables i am actually working of[code="sql"]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))LOCK ALLPAGESgoIF OBJECT_ID('dbo.IRBT') IS NOT NULL PRINT '<<< CREATED TABLE dbo.IRBT >>>'ELSE PRINT '<<< FAILED CREATING TABLE dbo.IRBT >>>'goGRANT DELETE ON dbo.IRBT TO OPXGRPgoGRANT DELETE ON dbo.IRBT TO FIRECALLgoGRANT INSERT ON dbo.IRBT TO OPXGRPgoGRANT INSERT ON dbo.IRBT TO FIRECALLgoGRANT REFERENCES ON dbo.IRBT TO OPXGRPgoGRANT REFERENCES ON dbo.IRBT TO FIRECALLgoGRANT SELECT ON dbo.IRBT TO DEVLgoGRANT SELECT ON dbo.IRBT TO OPXGRPgoGRANT SELECT ON dbo.IRBT TO FIRECALLgoGRANT UPDATE ON dbo.IRBT TO OPXGRPgoGRANT UPDATE ON dbo.IRBT TO FIRECALLgo[/code]table 2[code="sql"]CREATE TABLE dbo.ACRT(CNO CHAR (50) NOT 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,NEGWATCH bit DEFAULT 0 NOT NULL,)LOCK ALLPAGESgoIF OBJECT_ID('dbo.ACRT') IS NOT NULL PRINT '<<< CREATED TABLE dbo.ACRT >>>'ELSE PRINT '<<< FAILED CREATING TABLE dbo.ACRT >>>'goGRANT DELETE ON dbo.ACRT TO OPXGRPgoGRANT DELETE ON dbo.ACRT TO FIRECALLgoGRANT INSERT ON dbo.ACRT TO OPXGRPgoGRANT INSERT ON dbo.ACRT TO FIRECALLgoGRANT REFERENCES ON dbo.ACRT TO OPXGRPgoGRANT REFERENCES ON dbo.ACRT TO FIRECALLgoGRANT SELECT ON dbo.ACRT TO DEVLgoGRANT SELECT ON dbo.ACRT TO OPXGRPgoGRANT SELECT ON dbo.ACRT TO FIRECALLgoGRANT UPDATE ON dbo.ACRT TO OPXGRPgoGRANT UPDATE ON dbo.ACRT TO FIRECALLGo[/code]insert for table 1[code="sql"]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")[/code]insert for table 2[code="sql"]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")[/code]so what im trying to do is if in the acrt table or table 2 if the NEGWATCH =1 then for MIDIRB in the acrt table or table 2 i want to add the next value from irbt or table 1.sosay negwatch =1 and midird before this update is 16 then when update is done and from the irtb table or table 1 the midirb = 20 as the next number in the irbt table after 16 is 20hope this is better explained but not so sureFri, 05 Oct 2012 08:29:29 GMTronan.healyhttp://www.sqlservercentral.com/Forums/Topic1369060-391-1.aspx[quote][b]ronan.healy (10/5/2012)[/b][hr]hi i have 2 tables in 1 i have the followingrateN1 rateN2 rateN3 ratea aa aaa 1b bb bbb 2c cc ccc 4d dd ddd 6table 2 has the followingrateAge rateN rate y/nmood a 1 nfitch bb 2 ybut i want it to be the 4 as thats the next number up from the 2 will that sql still workanyone any ideas how to do this[/quote]Hi and welcome to SSC. From what you posted there is zero chance that anybody can help. There is nowhere near enough information. Can you post ddl (create table scripts), sample data (insert statements) and desired output based on your sample data? Take a look at the first link in my signature for best practices when posting questions.Fri, 05 Oct 2012 08:13:07 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1369060-391-1.aspxhi i have 2 tables in 1 i have the followingrateN1 rateN2 rateN3 ratea aa aaa 1b bb bbb 2c cc ccc 4d dd ddd 6table 2 has the followingrateAge rateN rate y/nmood a 1 nfitch bb 2 ybut i want it to be the 4 as thats the next number up from the 2 will that sql still workanyone any ideas how to do thisFri, 05 Oct 2012 08:00:50 GMTronan.healy