Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

update table with next value Expand / Collapse
Author
Message
Posted Friday, October 5, 2012 8:00 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:02 AM
Points: 77, Visits: 144
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
Post #1369060
Posted Friday, October 5, 2012 8:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 13,138, Visits: 11,977
ronan.healy (10/5/2012)
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


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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1369082
Posted Friday, October 5, 2012 8:29 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:02 AM
Points: 77, Visits: 144
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,
IRBRATING numeric (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


Post #1369094
Posted Friday, October 5, 2012 8:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 13,138, Visits: 11,977
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1369108
Posted Friday, October 5, 2012 8:50 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:02 AM
Points: 77, Visits: 144
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,

Post #1369110
Posted Friday, October 5, 2012 8:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 13,138, Visits: 11,977
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1369111
Posted Friday, October 5, 2012 8:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:02 AM
Points: 77, Visits: 144
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,






)

Post #1369114
Posted Friday, October 5, 2012 8:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 13,138, Visits: 11,977
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1369121
Posted Friday, October 5, 2012 9:03 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:02 AM
Points: 77, Visits: 144
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
Post #1369124
Posted Friday, October 5, 2012 9:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 13,138, Visits: 11,977
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1369129
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse