October 5, 2012 at 8:00 am
hi i have 2 tables
in 1 i have the following
rateN1 rateN2 rateN3 rate
a aa aaa 1
b bb bbb 2
c cc ccc 4
d dd ddd 6
table 2 has the following
rateAge rateN rate y/n
mood a 1 n
fitch bb 2 y
but i want it to be the 4 as thats the next number up from the 2 will that sql still work
anyone any ideas how to do this
October 5, 2012 at 8:13 am
ronan.healy (10/5/2012)
hi i have 2 tablesin 1 i have the following
rateN1 rateN2 rateN3 rate
a aa aaa 1
b bb bbb 2
c cc ccc 4
d dd ddd 6
table 2 has the following
rateAge rateN rate y/n
mood a 1 n
fitch bb 2 y
but i want it to be the 4 as thats the next number up from the 2 will that sql still work
anyone any ideas how to do this
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.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 5, 2012 at 8:29 am
hi
thanks for response
ok will try post it better
he is the two tables i am actually working of
CREATE TABLE dbo.IRBT
(
MOODYSRATE CHAR (10) NOT NULL,
SNPRATE CHAR (10) NOT NULL,
FITCHRATE CHAR (10) NOT NULL,
IRBRATINGnumeric (4) NOT NULL,
CONSTRAINT IRBTPK
PRIMARY KEY CLUSTERED (MOODYSRATE)
)
LOCK ALLPAGES
go
IF OBJECT_ID('dbo.IRBT') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.IRBT >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.IRBT >>>'
go
GRANT DELETE ON dbo.IRBT TO OPXGRP
go
GRANT DELETE ON dbo.IRBT TO FIRECALL
go
GRANT INSERT ON dbo.IRBT TO OPXGRP
go
GRANT INSERT ON dbo.IRBT TO FIRECALL
go
GRANT REFERENCES ON dbo.IRBT TO OPXGRP
go
GRANT REFERENCES ON dbo.IRBT TO FIRECALL
go
GRANT SELECT ON dbo.IRBT TO DEVL
go
GRANT SELECT ON dbo.IRBT TO OPXGRP
go
GRANT SELECT ON dbo.IRBT TO FIRECALL
go
GRANT UPDATE ON dbo.IRBT TO OPXGRP
go
GRANT UPDATE ON dbo.IRBT TO FIRECALL
go
table 2
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 ALLPAGES
go
IF OBJECT_ID('dbo.ACRT') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.ACRT >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.ACRT >>>'
go
GRANT DELETE ON dbo.ACRT TO OPXGRP
go
GRANT DELETE ON dbo.ACRT TO FIRECALL
go
GRANT INSERT ON dbo.ACRT TO OPXGRP
go
GRANT INSERT ON dbo.ACRT TO FIRECALL
go
GRANT REFERENCES ON dbo.ACRT TO OPXGRP
go
GRANT REFERENCES ON dbo.ACRT TO FIRECALL
go
GRANT SELECT ON dbo.ACRT TO DEVL
go
GRANT SELECT ON dbo.ACRT TO OPXGRP
go
GRANT SELECT ON dbo.ACRT TO FIRECALL
go
GRANT UPDATE ON dbo.ACRT TO OPXGRP
go
GRANT UPDATE ON dbo.ACRT TO FIRECALL
Go
insert for table 1
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 for table 2
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")
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.
so
say 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 20
hope this is better explained but not so sure
October 5, 2012 at 8:46 am
Well 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.
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.
so
say 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 20
hope this is better explained but not so sure
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.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 5, 2012 at 8:50 am
sorry put in the older version of table
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,
October 5, 2012 at 8:51 am
ronan.healy (10/5/2012)
sorry put in the older version of table
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,
This is incomplete. Is this the table ACRT now? Is NEGWATCH the last column?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 5, 2012 at 8:54 am
ya this is the acrt table now as i have it in our system and negwatch is last column
probably missing the last braket didnt copy and past the lot thought i had sorry
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,
)
October 5, 2012 at 8:59 am
No problem.
So the ddl for ACRT should look like this?
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
)
I need some explanation as to what you are trying to do.
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.
so
say 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 20
hope this is better explained but not so sure
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?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 5, 2012 at 9:03 am
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 table
so if its 2 next is 3
id its 3 next is 4
if its 16 next is 20
October 5, 2012 at 9:07 am
ronan.healy (10/5/2012)
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 table
so if its 2 next is 3
id its 3 next is 4
if its 16 next is 20
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.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 5, 2012 at 9:13 am
hi
i 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 oder
2
3
4
5 etc
October 5, 2012 at 9:16 am
ronan.healy (10/5/2012)
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 oder
2
3
4
5 etc
So you are saying that we can use IRBRATING as the order?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 5, 2012 at 9:22 am
ya just double checked there and its ordered on the irbrating
October 5, 2012 at 9:41 am
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
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 5, 2012 at 9:46 am
thanks very much for help will try that
and also will try improve my [post in future
again thanks
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply