Increment a Column By 1 with SQL

  • Thread starter Thread starter Mike Thomas
  • Start date Start date
M

Mike Thomas

Is there an SQL statement which would populate the 'ItemNo' column below;
incrementing the ItemNo column by 1 for each row involved? I can do it
procedurally, and I suppose I could use a function with a static variable -
is there anything a little more graceful?

UPDATE TableX SET ItemNo = ???? Where FKey = 3 Order By Name

Before:

PrimeKey FKey Name ItemNo

4 3 Al null
3 3 Bob null
1 3 Carl null
6 3 Don null

After:

PrimeKey FKey Name ItemNo

4 3 Al 1
3 3 Bob 2
1 3 Carl 3
6 3 Don 4

Many thanks,
Mike Thomas
 
No there isn't.
I think you'll have to extract the names you are interested in, assign the
sequence numbers then join the result with the original table.
Why on earth are you doing this?

-Dorian
 
Here is a function that will do what you want.
The trick is that it that for the query to call it for every row, it has to
contain a reference to a field in the query; otherwise, it only calls it once
at the beginning and assigns 1 to all rows.

Another item to consider is that because it is in a common module, it will
retain it's value as long as the database is open, so we have to have a way
to tell it to reset to 0. In this case, True means set the return variable
to 0 so the next time it is called it will return 1 and False means return
the next incremental number.

In the call syntax below, I include a check of a field in the row that I
know will always return false.

So what you have to do to ensure it will start numbering at 1 is to call it
before you run the query with the value of true:

lngNothing = GetNextValue(True)

Public Function GetNextValue(strReset As Boolean) As Long
Static lngNextNum As Long
If strReset Then
lngNextNum = 0
Else
lngNextNum = IIf(lngNextNum = 0, 1, lngNextNum + 1)
End If
GetNextValue = lngNextNum
End Function

ANum: getnextvalue(IsNull([levels]![level_two]))
 
Mike said:
Is there an SQL statement which would populate the 'ItemNo' column below;
incrementing the ItemNo column by 1 for each row involved? I can do it
procedurally, and I suppose I could use a function with a static variable -
is there anything a little more graceful?

UPDATE TableX SET ItemNo = ???? Where FKey = 3 Order By Name

Before:

PrimeKey FKey Name ItemNo

4 3 Al null
3 3 Bob null
1 3 Carl null
6 3 Don null

After:

PrimeKey FKey Name ItemNo

4 3 Al 1
3 3 Bob 2
1 3 Carl 3
6 3 Don 4
Click to expand...


Try this:

UPDATE TableX
SET ItemNo = DCount("*", "TableX ",
"FKey = " & FKey &
" AND Name <= '" & [Name] & "' ")
WHERE FKey = 3
 
Back
Top