Jst a query......

Question

Post reply

Jst a query......

Maddy...!

SSC Eights!

Points: 995
More actions
May 27, 2010 at 4:10 am

#235559

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

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

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Investigate the row_number() function

Clear Sky SQL
My Blog[/url]

Maddy...! SSC Eights! Points: 995 More actions · Answer 2

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

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]

Mitesh Oswal SSCrazy Points: 2798 More actions · Answer 4

;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

Paul White SSC Guru Points: 150467 More actions · Answer 5

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 😀

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Maddy...! SSC Eights! Points: 995 More actions · Answer 6

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

Mitesh Oswal SSCrazy Points: 2798 More actions · Answer 7

Dear,

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

Regards,
Mitesh OSwal
+918698619998

Paul White SSC Guru Points: 150467 More actions · Answer 8

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;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Maddy...! SSC Eights! Points: 995 More actions · Answer 9

Maddy...!

SSC Eights!

Points: 995

May 27, 2010 at 4:42 am

#1173226

tan Q

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

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.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 11

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 Dixie Flatline SSC Guru Points: 53354 More actions · Answer 12

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

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.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 14

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)