Jst a query......

  • hi my dear techies......

    i have a smalll query for u all

    if possible solve it in a single query without cursors

    ie.,

    i have a table with the structure like this

    create table dbo.ttest ( doco int,doctype nvarchar(2),cpnt int)

    insert into dbo.ttest

    (doco,doctype)

    select 1,'a'

    union all

    select 1,'a'

    union all

    select 2,'a'

    union all

    select 2,'a'

    union all

    select 2,'b'

    union all

    select 1,'b'

    now i want an update statement to update the column cpnt

    so that the similar doco,doctype data will have their own set starting from 1

    after update the data should be

    doco doctype cpnt

    1a1

    1a2

    1a3

    1a4

    1b1

    1b2

    2a1

    2a2

    2a3

    2a4

    2b1

    2b2

  • Investigate the row_number() function



    Clear Sky SQL
    My Blog[/url]

  • sorry buddy.............. if u r really talented enough jst solve it here itself

  • Maddy...! (5/27/2010)


    sorry buddy.............. if u r really talented enough jst solve it here itself

    No, i wont. It is trivial. Read Books On Line for 30seconds and you will see how trivial.



    Clear Sky SQL
    My Blog[/url]

  • ;with cte as

    (

    select *,ROW_NUMBER() Over(partition by ttest.doctype ,ttest.doco order by ttest.doco) AS Rn from ttest,

    (select t.doctype as Rn1 from ttest t group by t.doctype)a

    )

    select doco,doctype,rn from cte

    Regards,
    Mitesh OSwal
    +918698619998

  • Dave Ballantyne (5/27/2010)


    Read Books On Line for 30 seconds and you will see how trivial.

    There's no way it should take 30 seconds 😀

  • i asked you for an update statement

    not the select

    need to update the same column which is generated by this select in the table with corresponding data

  • Dear,

    You have 6 column in table and you wanna to update 12 ?

    Regards,
    Mitesh OSwal
    +918698619998

  • UPDATE LazyMaddy

    SET cpnt = LazyMaddy.q

    FROM (

    SELECT T.cpnt,

    q = ROW_NUMBER() OVER (

    PARTITION BY T.doco, T.doctype

    ORDER BY (SELECT 0))

    FROM dbo.ttest T

    ) LazyMaddy;

  • tan Q

  • Paul White NZ (5/27/2010)


    UPDATE LazyMaddy

    SET cpnt = LazyMaddy.q

    FROM (

    SELECT T.cpnt,

    q = ROW_NUMBER() OVER (

    PARTITION BY T.doco, T.doctype

    ORDER BY (SELECT 0))

    FROM dbo.ttest T

    ) LazyMaddy;

    Love the table name you used, Paul. Too bad the sarcasm was missed by the OP.

    Maddy, just so you know, we are all volunteers here on SSC. We help because we have been helped. We are NOT here to do your work for you. Your response to Dave was totally uncalled for, he provided you with assistance telling you where you needed to look. You did NOTHING to help yourself. It would have taken you less time to look up what you needed in BOL than it took to post and wait here on SSC. Your problem was quite trivial in nature. If, after reading about the row_count() function, you still had questions you could have easily come back to this thread and asked for additional help.

  • Heh... I remember not being able to do this type of stuff especially since UPDATE requires a bit of different handling than just a simple SELECT. No problem is trivial when you're under the gun and you just don't know how to do something. 😉 The OP even took the time to post data correctly.

    I do, however, agree that a nicer response to Dave's suggestion would have been much more appropriate.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • The response was rude, insulting, and totally inappropriate. It earned the OP a spot on my personal blacklist.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Jeff Moden (5/27/2010)


    Heh... I remember not being able to do this type of stuff especially since UPDATE requires a bit of different handling than just a simple SELECT. No problem is trivial when you're under the gun and you just don't know how to do something. 😉 The OP even took the time to post data correctly.

    I do, however, agree that a nicer response to Dave's suggestion would have been much more appropriate.

    I guess it depends on one's definition of trivial. If you can easily generate a SELECT statement that returns a result set that shows both current and future values, it isn't that hard to use that as input in an UPDATE statement, IMHO.

  • Lynn Pettis (5/27/2010)


    Jeff Moden (5/27/2010)


    Heh... I remember not being able to do this type of stuff especially since UPDATE requires a bit of different handling than just a simple SELECT. No problem is trivial when you're under the gun and you just don't know how to do something. 😉 The OP even took the time to post data correctly.

    I do, however, agree that a nicer response to Dave's suggestion would have been much more appropriate.

    I guess it depends on one's definition of trivial. If you can easily generate a SELECT statement that returns a result set that shows both current and future values, it isn't that hard to use that as input in an UPDATE statement, IMHO.

    I do remember a day when I couldn't do such a thing if my life depended on it. Just because it's a trivial task for me now doesn't make it any less trivial then. 😉

    No question, though. The OP was terribly rude. Looking back at the OPs other posts, it doesn't seem to be the "norm" to be rude and I can't help but think the OP was under the gun and took it out on the wrong folks. Heh... none of us has ever done that before, huh? :hehe:

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply