update table with next value

  • 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

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

  • 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

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

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

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

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

    )

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

  • 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

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

  • 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

  • ronan.healy (10/5/2012)


    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

    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/

  • ya just double checked there and its ordered on the irbrating

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

  • 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