Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » update table with next value
Add to briefcase
21 posts, Page 1 of 3
123»»»

update table with next value Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
ronan.healy
Posted Friday, October 05, 2012 8:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 4:44 AM
Points: 14, Visits: 44
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
Sean Lange
Posted Friday, October 05, 2012 8:13 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 8,592, Visits: 8,233
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
Post #1369082
ronan.healy
Posted Friday, October 05, 2012 8:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 4:44 AM
Points: 14, Visits: 44
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
Sean Lange
Posted Friday, October 05, 2012 8:46 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 8,592, Visits: 8,233
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
Post #1369108
ronan.healy
Posted Friday, October 05, 2012 8:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 4:44 AM
Points: 14, Visits: 44
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
Sean Lange
Posted Friday, October 05, 2012 8:51 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 8,592, Visits: 8,233
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
Post #1369111
ronan.healy
Posted Friday, October 05, 2012 8:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 4:44 AM
Points: 14, Visits: 44
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
Sean Lange
Posted Friday, October 05, 2012 8:59 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 8,592, Visits: 8,233
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
Post #1369121
ronan.healy
Posted Friday, October 05, 2012 9:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 4:44 AM
Points: 14, Visits: 44
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
Sean Lange
Posted Friday, October 05, 2012 9:07 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 8,592, Visits: 8,233
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
Post #1369129
« Prev Topic | Next Topic »

Add to briefcase
21 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.