[RESOLVED] SQL Update question

[DataMiser]

I find myself needing to update a rather large table with a new field.
What I need is for this new field to hold basically a row number. There are already a lot of records in the table and the rownumber field was just added so it is 0 on every row.

What I am wondering is. Is there a simple query that can update every existing row with a value so that each row gets the next sequential value or am I going to need to loop through the db and do them all one by one?

Thanks

[Zvoni]

What DBMS?
Exact version please, because there is a way, but depends on the Version (MySQL for example)

[DataMiser]

The DB is FoxPro I think version 8, though I am trying to execute the query through code from within my program.

I tried looping through and updating with a simple VB6 piece and it failed after just under 1500 records. The issue there being that there are cases where more than one row can contain the same data which is the reason for adding the new field so there is a way to determine which of those rows to update when needed.

I do not know if this can be done given the current state of the data, I would like to avoid having to clear the table if at all possible.

[Zvoni]

Ouch.

hmm.... if it's FoxPro (which is a Sub/Superset of DBF):
1) Make a backupcopy of that table
2) Import that Table into Excel
3) In Excel in that additional Column do this "enter the first number and pull the mouse down" self-numbering magic
4) Export that sheet to CSV
5) Import that CSV into FoxPro (a new Table)
6) Compare your original Table with that one
7) If OK replace original table with that one

If it were anything else i could give you a simple SQL_Statement
OTOH, FoxPro has something called RECNO: https://learn.microsoft.com/en-us/pr...ectedfrom=MSDN

[sapator]

Fox pro, no idea. If it has a max function somewhere then:

declare @i bigint  
set @i = (SELECT ISNULL(MAX([newid]),0)  FROM  [Table_4] )

update  [Table_4]
set [newid]  = @i , @i = @i + 1

[DataMiser]

Thanks for that info on RecNo()
Turns out to be simple after all

Update TableName Set RowNO=RecNO()

Worked like a charm

[Zvoni]

Thanks for that info on RecNo()
Turns out to be simple after all

Update TableName Set RowNO=RecNO()

Worked like a charm

Seriously????
I feel like a blind chicken poking in a haystack.... LOL

[DataMiser]

LOL

Yep, I was very surprised but had to try it and see, went through and looked at the data and everything looks good,

Thanks again.