updating using incremental values
-
Hi,
I have a problem. I have a table with three columns, column 1 is an int which can have multiple of the same number, column two is random text, column three is filled with null values.
I need to update the column with null values (column 3) using incrementing numbers starting at 0 and grouping on column 1. The increment needs to be reset to 0 every time a new number is encountered in column 1.
declare @varX int
update CText_D
set D_DocNum = select @varX = @varX + 1
where D_SEQ = 4
I set D_SEQ = 4 (column 1) just for testing to see if I could get it to work just for the 4's.
Anyone have any idea's, I am really stumped, I thought about loops because it is a one time update so speed isn't as important but I don't know what I could loop on.
-
Check this thread out... not for the faint of heart
. -
Thank-you very much, it is just what I was looking for.
-
HTH.
-
Here is a copy of the design of my table:
[Column Name][Data Type][Length][Allow Null]
1 T_SEQ int 4 1
0 T_Text char 80 1
0 T_DocNum int 4 1
It turns out I didn't need to use T_Text to resolve the issue.
From Remi Gregoire I got the following resolution after a bit of tweeking which solved my issue.
Declare @Int as int,
@LastId as int
set @Int = -1
set @LastId = -1
Update dbo.CText_T
Set @Int = T_DocNum = Case When @LastId <> T_SEQ THEN -1
ELSE @Int
END + 1,
@LastId = T_SEQ
-
I am not called a Newbie for nothing
I didn't really care if it would port since it is a one time update that needed to be run. With the table I had it really isn't possible to get something that would be more predictable. Since I used the GUI interface for creating the table I don't really have any DDL (thanks for explaining what it was), however here is some sample data taken after I ran the update.
2 APPOINTS, ROY L. PATTERSON 0
3 BEWARE JOHN C & JOHN JAY OWENS ARE THE SAME SEE 14-16842 0
4 BANK OF ASTORIA, MORTGAGE HOLDER 0
5 INTERWEST AQUIIRED HOME SAVINGS & LOAN SEE COPY IN 14-16742 0
6 COPY OF 1982 BANKRUPTCY IN FILE 14-16752 0
8 COPY OF BANKRUPTCY IN 14-17886, REVIEW BEFORE ISSUING ANY PRELIMS 0
9 MEMO ON FRAUD/CHICAGO TITLE DATED 8/20/91 SEE NOTEBOOK 0
10 WARNING! IF ASKED TO EARLY ISSUE CALL JEFF STEFFENS/TRANS FOR INFO 0
11 COPY IN 14-18849 0
15 CALL CHICAGO LEGAL COUNCEL IF ORDERS INVOLVE THESE NAMES 0
16 INTERFIRST MERGED WITH STANDARD 0
17 COPY IN 14-21664 OF THE JERRY D SITTNER PENSION TRUST 0
23 APPOINTS DAVID BODWAY 0
Don't forget this issue has been resolved so don't spend too much time on it
I will try and remember all this next time I have a question. -
Thank you for your VERY considerate reply, Jereme Guenther!
I wasn't born stupid - I had to study.
-
Don't wanna insult you Joe, but follow the liink and read the whole thing. I strongly suggested using the answer you provided instead of the one he chose. The other one was more for run than anything else.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply