refer from subquery to current record

Question

refer from subquery to current record

rein-863529

Valued Member

Points: 57
More actions
October 27, 2008 at 9:03 am

#195241

Identificators in Table1 are "with spaces" (some records are deleted).
I made a new field TightNumbering. How to fill it?
Update Table1 set TightNumbering=(select count(*) from Table1 where ID<CurrentRecordID)
What to put there into the place of "CurrentRecordID"?
Thanks in advance!
Rein

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

your post is a little thin on details, and hard to follow, so I'll post a best guess of how to do an UPDATE FROM based on your statement:

Update Table1

set Table1.TightNumbering = MYSUBSELECT.TOTALCOUNT

FROM(select ID,count(*) AS TOTALCOUNT from Table1 GROUP BY ID) MYSUBSELECT

WHERE where Table1.ID =MYSUBSELECT.ID

AND Table1.TightNumbering <> MYSUBSELECT.TOTALCOUNT

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Garadin One Orange Chip Points: 29613 More actions · Answer 2

It seems to me that you're trying to establish a running count of rows in the table.. Please Note, you MUST have a clustered index on your current ID column for this to work.

DECLARE @TN int,

@ID int

SET @TN = 0

UPDATE Table1

SET @TN = TightNumbering = @TN + 1,

@ID = ID

FROM Table1 WITH (INDEX(Your_Clustered_Index_Name))

For more information on this method, review the following article: http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

If Lowell and myself are both incorrect as to your end goal here, please clarify your question.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

rein-863529 Valued Member Points: 57 More actions · Answer 3

Thank you!

Sorry about badly posted question. I tried to avoid babbling, but it seems that too hard tried. 🙂 And of course there is a problem with my weak English. I thought that "TightNumbering" tells the goal clearly, but seems that crude translation from Estonian gave wrong result.

Seth understood my goal perfectly. (I'll try to remember that term - "running count") And your example works fine. The only difference - I tried to make it with one UPDATE-query. But I can use it too.

I didn't understand - what does there that @ID? I cant find out, for what is that useful? Or is that again about my badly posted question? :'-( Sorry again.

rein-863529 Valued Member Points: 57 More actions · Answer 4

I rejoiced too early.

Problem - in my first post I left out part of goal. Just to make question easier.

Actually I wanted to make "running count" in space of one year. Also something like:

Update Table1

set DocNumberInCurrentYear=

(select count(*) from Table1 where ID<CurrentRecordID and Year(DocDate)=CurrentRecordDate)

Garadin One Orange Chip Points: 29613 More actions · Answer 5

Do you mean you want to start over the count each year with 1?

Also, as for the @ID, it serves no real purpose except to "anchor" that query. People have found weird issues omitting it. Jeff explains it all in that article (it's his method).

Starting the year over with 1 isn't a problem, you just add another case.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]